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?
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.