对于PostgreSQL v15.2,我使用以下表定义:
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 =)
)
我看到这个查询的查询计划:
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)
查询计划正确地使用了btree_gist索引,但随后它对由双时态时间范围过滤的frequent_flyer_id进行位图索引扫描,最后对frequent_flyer_id条件进行重新判断.为什么在初始btree_gist索引扫描之后需要执行后续步骤?
我希望看到一个查询计划,它只包含使用btree_gist索引的索引扫描.额外的步骤是必要的,因为gist索引是有损的,因此后续步骤是判断假阳性?