Sample Header Ad - 728x90

Why is a GiST index used for filtering on non-leading column?

6 votes
1 answer
2143 views
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)
*dbfiddle [here](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=e40988cd9745f7cd2949e844ceb294f0)*
Asked by Kejlo (63 rep)
Jul 12, 2020, 09:05 PM
Last activity: Jul 13, 2020, 11:51 AM