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)