16

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
nourdine
  • 7,407
  • 10
  • 46
  • 58

2 Answers2

19

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
3

the 3 fields cannot go under the same index because of their nature

The 3 fields can go under the same index using the btree-gist module.

Rafael Eyng
  • 4,720
  • 2
  • 33
  • 34
Richard Huxton
  • 21,516
  • 3
  • 39
  • 51
  • 1
    oh I see... I cannot get how to create multi column indexes though :( can you? It only show: `CREATE INDEX testidx ON test USING gist (a);` but that is not multicolumn, is it? – nourdine Mar 26 '14 at 11:53
  • Does it not work if you just (1) load the extension and (2) create it with the multiple columns listed as you would normally do? – Richard Huxton Mar 26 '14 at 12:52
  • 1
    oh ok so I need to load an extension to be able to do that. All clear now. Thanks. – nourdine Mar 26 '14 at 13:26