4

I always learned and understood that an index can only be used when we have predicates for the leading (or all) columns. Now, to my surprise, I noticed that a GiST index is used in the following query. Why is that? Is this a special feature of GiST indexes?

CREATE TABLE t1 (
    i INT, 
    j INT, 
    k INT
);

INSERT INTO t1 
SELECT i, j, k 
FROM   GENERATE_SERIES(1, 100) AS i, 
       GENERATE_SERIES(1, 100) AS j, 
       GENERATE_SERIES(1, 100) AS k;

CREATE INDEX ON t1 USING GiST(i, j, k);

EXPLAIN SELECT * FROM t1 WHERE k = 54;
QUERY PLAN
Bitmap Heap Scan on t1  (cost=199.03..5780.51 rows=5000 width=12)
  Recheck Cond: (k = 54)
  ->  Bitmap Index Scan on t1_i_j_k_idx  (cost=0.00..197.78 rows=5000 width=0)
        Index Cond: (k = 54)

db<>fiddle here

4

an index can only be used when we have predicates for the leading (or all) columns.

In Postgres, this rule of thumb is only somewhat applicable to (default) B-tree indexes. See:

But mostly wrong for GiST indexes. The manual:

A multicolumn GiST index can be used with query conditions that involve any subset of the index's columns. Conditions on additional columns restrict the entries returned by the index, but the condition on the first column is the most important one for determining how much of the index needs to be scanned. A GiST index will be relatively ineffective if its first column has only a few distinct values, even if there are many distinct values in additional columns.

So, generally, you put columns (or expressions) with the most distinct values first in a GiST index.

Related:

It's different for other index types. For (also very common) GIN indexes:

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.

The whole chapter of the manual is recommended reading.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.