HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
leadingwhynoncolumnusedgistforindexfiltering

Problem

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

Solution

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:

  • Working of indexes in PostgreSQL



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:

  • PostgreSQL exclusion constraints in a bitemporal setting?



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.

Context

StackExchange Database Administrators Q#270814, answer score: 6

Revisions (0)

No revisions yet.