Multicolumn index on 3 fields with heterogenous data types
Single-column index
Postgres can combine multiple indexes very efficiently in a single query with bitmap index scans. Most of the time, the most selective index is picked (or two, combined with bitmap index scans) and the rest is filtered. Once the result set is narrow enough, it's not efficient to scan more indexes.
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. The manual:
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 or both. It depends.
For that, you need either btree_gin
or btree_gist
or both, respectively.
the 3 fields cannot go under the same index because of their nature
Oh yes they can.