postgres not using btree_gist index

Question

I have a huge table with a primary key and a btree_gist index. When I query for the columns in the btree_gist index, I would expect that the index is used and the query performs quite fast. However, the optimiser always performs an index scan on the primary key and the filters.

Example:

create table test1 (
    id1 bigint not null,
    id2 bigint not null,
    validtime tstzrange not null,
    data float);
alter table test1 add constraint pk_test1 primary key (id1, id2, validtime);
alter table test1 add constraint ex_test1_validtime exclude using gist (id1 with =, id2 with =, validtime with &&);

The table contains about 1.2 billion rows, the query I wonder about returns only some hundred rows, but takes a long time:

select * from test1 where id1=1 and id2=1 and validtime && '[2020-01-01,2020-02-01)';
(about 3s)

Query plan:

explain select * from test1 where id1=1 and id2=1 and validtime && '[2020-01-01,2020-02-01)';
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Index Scan using pk_test1 on test1  (cost=0.70..24.68 rows=1 width=46)
   Index Cond: ((id1 = 1) AND (id2 = 1))
   Filter: (validtime && '["2020-01-01 00:00:00+00","2020-02-01 00:00:00+00")'::tstzrange)

The reason for the bad performance is obviously that ten thousands of rows are read and filtered in the time criteria.

I wonder why postgres does not use the btree_gist.

I have another, slightly different table where the btree_gist is used but in a very different way than I would expect. This table has about 160 million rows.

create table test2 (
    id1 bigint not null,
    validtime tstzrange not null);                                                                                                                                                                                                                                                                                                                                              
alter table test2 add constraint pk_test2 primary key (id1, validtime);
alter table test2 add constraint ex_test2_validtime exclude using gist (id1 with =, validtime with &&);

Here, the execution plan looks like this:

select * from test2 where id1=1 and validtime && '[2020-01-01,2020-02-01)';
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test2  (cost=1933.19..1937.20 rows=1 width=62)
   Recheck Cond: ((id1 = 1) AND (validtime && '["2020-01-01 00:00:00+00","2020-02-01 00:00:00+00")'::tstzrange))
   ->  BitmapAnd  (cost=1933.19..1933.19 rows=1 width=0)
         ->  Bitmap Index Scan on pk_test2  (cost=0.00..574.20 rows=11417 width=0)
               Index Cond: (id1 = 1)
         ->  Bitmap Index Scan on ex_test2_validtime  (cost=0.00..1358.74 rows=17019 width=0)
               Index Cond: (validtime && '["2020-01-01 00:00:00+00","2020-02-01 00:00:00+00")'::tstzrange)

Why the two bitmap index scans, couldn't it all be done with one index scan using the btree_gist index?

Please post EXPLAIN (ANALYZE, BUFFERS) output. About your first query: is the desired index used if you drop the other one? — Laurenz Albe
– Laurenz Albe, Commented Apr 7, 2022 at 20:03
Unfortunately I can't test much with test1 currently, as I'm building a new index and the table is locked. I created another test table (with no data) to try without the primary key. The query plan shows then a bitmap scan on the btree_gist index, but only with the time criteria, then a bitmap heap scan on the table and filtering for the id1 and id2 criteria. I guess this will be a disaster on the real, billion row table (although better than a full table scan). What I don't understand is why the id1 and id2 criteria don't go into the btree_gist index scan. I think I'm missing something. — paul
– paul, Commented Apr 7, 2022 at 20:45

Erwin Brandstetter · Accepted Answer · 2022-04-07 22:48:01Z

6

Finally found it:

The indexes are not used because of a type mismatch between the query and the index. It's actually mentioned all over the places, but I just read over it.

1 is obviously not a bigint! Funny that the cast happens automatically for using the btree primary key, but not the btree_gist.

Anyhow, everything works as expected with this query:

select * from test1
where id1=1::bigint and id2=1::bigint
and validtime && '[2020-01-01,2020-02-01)';

Learning this cost me some hours, I will never forget it again!

edited Apr 7, 2022 at 22:48

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

answered Apr 7, 2022 at 21:12

paul

4782 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Laurenz Albe · Accepted Answer · 2022-04-08 05:48:39Z

Your answer is correct, but I want to add some background as to why this happens.

An index in PostgreSQL only supports the operators that belong to the operator family of its operator class. For GiST indexes on bigint, that is

SELECT ao.amoplefttype::regtype,
       op.oprname,
       ao.amoprighttype::regtype
FROM pg_opfamily AS of
   JOIN pg_am AS am ON of.opfmethod = am.oid
   JOIN pg_amop AS ao ON of.oid = ao.amopfamily
   JOIN pg_operator AS op ON ao.amopopr = op.oid
WHERE am.amname = 'gist'
  AND ao.amoplefttype = 'bigint'::regtype;

 amoplefttype │ oprname │ amoprighttype 
══════════════╪═════════╪═══════════════
 bigint       │ <       │ bigint
 bigint       │ <=      │ bigint
 bigint       │ =       │ bigint
 bigint       │ >=      │ bigint
 bigint       │ >       │ bigint
 bigint       │ <>      │ bigint
 bigint       │ <->     │ bigint
(7 rows)

which explains why you have to cast to bigint for the index to get used.

This is surprising if you are used to PostgreSQL, because PostgreSQL does not need such a cast with a B-tree index. The explanation is that the operator family for btree has more operators:

SELECT ao.amoplefttype::regtype,
       op.oprname,
       ao.amoprighttype::regtype
FROM pg_opfamily AS of
   JOIN pg_am AS am ON of.opfmethod = am.oid
   JOIN pg_amop AS ao ON of.oid = ao.amopfamily
   JOIN pg_operator AS op ON ao.amopopr = op.oid
WHERE am.amname = 'btree'
  AND ao.amoplefttype = 'bigint'::regtype;

 amoplefttype │ oprname │ amoprighttype 
══════════════╪═════════╪═══════════════
 bigint       │ <       │ bigint
 bigint       │ <=      │ bigint
 bigint       │ =       │ bigint
 bigint       │ >=      │ bigint
 bigint       │ >       │ bigint
 bigint       │ <       │ smallint
 bigint       │ <=      │ smallint
 bigint       │ =       │ smallint
 bigint       │ >=      │ smallint
 bigint       │ >       │ smallint
 bigint       │ <       │ integer
 bigint       │ <=      │ integer
 bigint       │ =       │ integer
 bigint       │ >=      │ integer
 bigint       │ >       │ integer
(15 rows)

and equality comparison between bigint and integer is among them.

You could have used a regular B-tree index to support your query if you had written the condition using >= and < rather than &&, which would make the cast unnecessary, but of course you don't want to create a second index if there is already the index from the exclusion constraint.

Curt Kolovson · Accepted Answer · 2024-04-08 16:40:13Z

0

Regarding your question: "Why the two bitmap index scans, couldn't it all be done with one index scan using the btree_gist index?" The planner is estimating that a bitmap scan may be more I/O efficient than the btree_gist index scan. See the answers to these related questions:

question about query plan using btree_gist index in PostgreSQL involving integer and tstzrange attributes
Why is Bitmap Scan faster than Index Scan when fetching a moderately large percentage of the table in PostgreSQL?

answered Apr 8, 2024 at 16:40

Curt Kolovson

215 bronze badges

Collectives™ on Stack Overflow

postgres not using btree_gist index

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related