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
