Postgres 13 beta3
set enable_bitmapscan=1; -- default
explain (analyze,buffers)
select *
from bigtable
where cars_ref = 1769854207 and t > '2020-01-01'::timestamp and t < '2021-01-01'::timestamp
limit 1
Short story. Big table > 100M rows. b-tree index on cars_ref, the t constraints limits it to one partition, but I don't think that is very relevant. In any case, running this query takes 1.5s:
Limit (cost=23728.33..23729.24 rows=1 width=635) (actual time=1516.865..1516.867 rows=1 loops=1)
Buffers: shared hit=2376
-> Bitmap Heap Scan on bigtable_y2020 bigtable (cost=23728.33..2530109.01 rows=2730872 width=635) (actual time=1516.863..1516.864 rows=1 loops=1)
Recheck Cond: (cars_ref = 1769854207)
Filter: ((t > '2020-01-01 00:00:00'::timestamp without time zone) AND (t < '2021-01-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=1
Buffers: shared hit=2376
-> Bitmap Index Scan on bigtable_y2020_cars_ref_idx (cost=0.00..23045.61 rows=2731965 width=0) (actual time=751.640..751.640 rows=2817675 loops=1)
Index Cond: (cars_ref = 1769854207)
Buffers: shared hit=2375
Planning Time: 0.365 ms
Execution Time: 1540.207 ms
1.5 seconds seems a lot for a single indexed row. I would think it should be instant, and so it is when I disable bitmap scan with: set enable_bitmapscan=0
Limit (cost=0.57..1.60 rows=1 width=636) (actual time=0.027..0.028 rows=1 loops=1)
Buffers: shared hit=5
-> Index Scan using bigtable_y2020_cars_ref_idx on bigtable_y2020 bigtable (cost=0.57..2966738.51 rows=2873818 width=636) (actual time=0.026..0.026 rows=1 loops=1)
Index Cond: (cars_ref = 1769854207)
Filter: ((t > '2020-01-01 00:00:00'::timestamp without time zone) AND (t < '2021-01-01 00:00:00'::timestamp without time zone))
Buffers: shared hit=5
Planning Time: 0.291 ms
Execution Time: 0.049 ms
But I am not supposed to disable bitmap scan! So why on earth do Postgres 13 beta3 think that returning 1 row, should be done with a bitmap scan?
I noticed that different values for "cars_ref" result in different plans in the query above. I belive that it has to do with wheter or not the cars_ref is in the "most common value" list. But in any case, I cant see why a bitmap scan is wise then you expect one row.
best regards
Klaudie