postgresql - Multicolumn index on 3 fields with heterogenous data types

Question

I have a postgres table with 3 fields:

• a : postgis geometry
• b : array varchar[]
• c : integer

and I have a query that involves all of them. I would like to add a multicolumn index to speed it up but I cannot as the 3 fields cannot go under the same index because of their nature.

What is the strategy in this case? Adding 3 indexes gist, gin and btree and postgres will use them all during the query?

Answer 1

Single-column index

First of all, Postgres can combine multiple indexes very efficiently in a single query with bitmap index scans. Most of the time, Postgres will pick the most selective index (or two and combine them with bitmap index scans) and filter the rest after a bitmap heap scan. Once the result set is narrow enough, it's not efficient to scan another index.

Multicolumn index

It is still faster to have a perfectly matching multicolumn index, but not by orders of magnitude.
Since you want to include an array type I suggest to use a GIN index. AFAIK, operator classes are missing for general-purpose GiST indexes on array type. (The exception being intarray for integer arrays.)

To include the integer column, first install the additional module btree_gin, which provides the necessary GIN operator classes. Run once per database:

CREATE EXTENSION btree_gin;

Then you should be able to create your multicolumn index:

CREATE INDEX tbl_abc_gin_idx ON tbl USING GIN(a, b, c);

The order of index columns is irrelevant for GIN indexes. Per documentation:

A multicolumn GIN index can be used with query conditions that involve any subset of the index's columns. Unlike B-tree or GiST, index search effectiveness is the same regardless of which index column(s) the query conditions use.

Nearest neighbour search

Since you are including a PostGis geometry type, chances are you want to do a nearest neighbour search, for which you need a GiST index. In this case I suggest two indexes:

CREATE INDEX tbl_ac_gist_idx ON tbl USING GiST(a, c);  -- geometry type
CREATE INDEX tbl_bc_gin_idx  ON tbl USING GIN(b, c);

You could add the integer column c to either one or both. It depends. For that, you need either btree_gin or btree_gist or both, respectively.