question about query plan using btree_gist index in PostgreSQL involving integer and tstzrange attributes

Question

With PostgreSQL v15.2, I'm using the following table definition:

   CREATE TABLE IF NOT EXISTS postgres_air_bitemp.frequent_flyer_transaction(
    frequent_flyer_transaction_key integer NOT NULL DEFAULT
    nextval('postgres_air_bitemp.frequent_flyer_transaction_frequent_flyer_transaction_key_seq'
      ::regclass),
    frequent_flyer_id integer NOT NULL,
    level text ,
    booking_leg_id integer,
    award_points integer,
    status_points integer,
    effective temporal_relationships.timeperiod NOT NULL,
    asserted temporal_relationships.timeperiod NOT NULL,
    row_created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT frequent_flyer_transaction_pk PRIMARY KEY (frequent_flyer_transaction_key),
    CONSTRAINT frequent_flyer_transaction_frequent_flyer_id_assert_eff_excl EXCLUDE USING gist (
        effective WITH &&,
        asserted WITH &&,
        frequent_flyer_id WITH =)
    )

I'm seeing the following query plan for this query:

    airlines=# explain analyze select * from
    postgres_air_bitemp.frequent_flyer_transaction t 
    where frequent_flyer_id=39189 and now()<@asserted and now()<@effective;
                                           QUERY PLAN                                                                              
# ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Bitmap Heap Scan on frequent_flyer_transaction t  (cost=4.69..87.91 rows=21 width=74) (actual time=0.097..0.099 rows=1 loops=1)
        Recheck Cond: (frequent_flyer_id = 39189)
        Filter: ((now() <@ (asserted)::tstzrange) AND (now() <@ (effective)::tstzrange))
        Heap Blocks: exact=1
        ->  Bitmap Index Scan on frequent_flyer_transaction_frequent_flyer_id_assert_eff_excl (cost=0.00..4.68 rows=21 width=0) (actual time=0.077..0.077 rows=1 loops=1)
            Index Cond: ((frequent_flyer_id = 39189) AND ((asserted)::tstzrange @> now()) AND ((effective)::tstzrange @> now()))
    Planning Time: 0.333 ms
    Execution Time: 0.172 ms
    (8 rows)

The query plan is correctly using the btree_gist index, but then it does a bitmap index scan on the frequent_flyer_id filtered by the bitemporal time ranges, and finally does a recheck on the frequent_flyer_id condition. Why are the subsequent steps necessary after the initial btree_gist index scan?

I was expecting to see a query plan that consisted solely of an index scan using the btree_gist index. Are the extra steps necessary because gist indexes are lossy, and therefore the subsequent steps are to check for false positives?

If you change the index to have frequent_flyer_id first, does it make a difference? — Laurenz Albe
– Laurenz Albe, Commented Apr 8, 2024 at 6:41
@LaurenzAlbe If you meant rebuilding the btree_gist index specifying (frequent_flyer_id WITH =, effective WITH &&, asserted WITH &&), that made no difference in the generated query plan. Isn't it the case that the bitmap index scan on the frequent_flyer_id, followed by the filter on bitemporal time ranges, followed by recheck condition on frequent_flyer_id are required to check for false positives because gist indexes are lossy? — Curt Kolovson
– Curt Kolovson, Commented Apr 8, 2024 at 12:49

jjanes · Accepted Answer · 2024-04-08 13:44:02Z

1

Since it expects to find 12 rows, it probably thinks the bitmap scan will lead to more efficient IO than a regular index scan would. You could set enable_bitmapscan=off and see what it switched to. The bitmap will always read the table in physical order, while an index scan will read it in some random order determined by the order of entries in the index.

All bitmaps are potentially lossy (if work_mem is exceeded). Since there is no way at planning time to gaurantee it won't go lossy, the mechanism to do rechecks is always set up. Then the line shows up in the plan, even if it goes completely unused.

I don't know exactly why the <@ conditions are rechecked by filter instead of by standard recheck. I think it has something to do with now() being only stable not immutable, as if I replace it with a literal timestamp then the check is moved into from the filter into the recheck. This doesn't make sense to me, it seems like it is a missed optimization, but a trivial one. It is easy to verify that this has nothing to do with btree_gist just by dropping btree_gist and creating a gist index just on the two time range columns, and seeing that the plan looks similar.

answered Apr 8, 2024 at 13:44

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

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

3 Comments

Curt Kolovson Over a year ago

When I specify timestamp literals instead of now(), the filter step goes away and moves to the recheck condition: `Recheck Cond: ((frequent_flyer_id = 39189) AND ('2024-04-08 08:24:52-07'::timestamp with time zone <@ (asserted)::tstzrange) AND ('2024-04-08 08:24:52-07'::timestamp with time zone <@ (effective)::tstzrange)) Heap Blocks: exact=1' You were right that the not immutable now() function is causing the filter.

Curt Kolovson Over a year ago

When I turn enable_bitmapscan off, the bitmap scan goes away, and resulting plan is just: ` Index Scan using frequent_flyer_transaction_frequent_flyer_id_assert_eff_excl on frequent_flyer_transaction t (cost=0.41..88.89 rows=21 width=74) (actual time=0.147..0.164 rows=1 loops=1) Index Cond: ((frequent_flyer_id = 39189) AND ((asserted)::tstzrange @> '2024-04-08 08:24:52-07'::timestamp with time zone) AND ((effective)::tstzrange @> '2024-04-08 08:24:52-07'::timestamp with time zone))`

jjanes Over a year ago

So it does think the bitmap is very slightly faster, just over 1% faster. That is probably down to the IO estimated as being very slightly more efficient, being fractionally more like sequential than random.

Collectives™ on Stack Overflow

question about query plan using btree_gist index in PostgreSQL involving integer and tstzrange attributes

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related