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
Best Answer
In Postgres, this rule of thumb is only somewhat applicable to (default) B-tree indexes. See:
But mostly wrong for GiST indexes. The manual:
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:
The whole chapter of the manual is recommended reading.