From: | Klaudie Willis <Klaudie(dot)Willis(at)protonmail(dot)com> |
---|---|
To: | PostgreSQL General <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Bitmap scan seem like such a strange choice when "limit 1" |
Date: | 2020-09-04 11:42:51 |
Message-ID: | X5rHjtT8gkMBFiaErcYyqD2R3r887Zxd9s15EutKCF2DxNxrxjm_JNp4YDyF-ANfXOr-xf-6PKoY5Lfq0X468FGrTJIh_KUaM4pXw8sum1s=@protonmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2020-09-04 12:14:15 | Re: Bitmap scan seem like such a strange choice when "limit 1" |
Previous Message | David Rowley | 2020-09-04 10:59:14 | Re: how to get top plan of GatherMerge in OSS10 |