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
Last activity: Jul 13, 2020, 11:51 AM