Bitmap scan seem like such a strange choice when "limit 1"

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

Responses

Browse pgsql-general by date

  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