public inbox for [email protected]
help / color / mirror / Atom feedWhy a bitmap scan in this case?
7+ messages / 3 participants
[nested] [flat]
* Why a bitmap scan in this case?
@ 2024-12-19 17:52 Jon Zeppieri <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Jon Zeppieri @ 2024-12-19 17:52 UTC (permalink / raw)
To: [email protected]
I'm trying to speed up a particular query, so I tried out a very
specific index meant to target this one query alone. (I'm not at all
convinced that's a good idea, but I'm curious to see just how fast I
can make this one query.) The index is like this:
create index idx_foo on my_tbl (start_on) where end_on is null and
bar_id is null and population_kind = 2;
The query needs to find rows that are before a certain start_on date
where all of the `where` conditions listed in the index are satisfied.
The query planner insists on using the index to do a bitmap scan:
```
db=# explain select start_on from my_tbl where end_on is null and
bar_id is null and population_kind = 2 and start_on < '2024-11-07';
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on my_tbl (cost=1116.28..329938.13 rows=317919 width=4)
Recheck Cond: ((start_on < '2024-11-07'::date) AND (end_on IS NULL)
AND (bar_id IS NULL) AND (population_kind = 2))
-> Bitmap Index Scan on idx_foo (cost=0.00..1036.81 rows=317919 width=0)
Index Cond: (start_on < '2024-11-07'::date)
JIT:
Functions: 2
Options: Inlining false, Optimization false, Expressions true, Deforming true
(7 rows)
```
Why wouldn't it do an index (or, really, an index only) scan in this
case, given that the index contains exactly the data that the query
needs?
I'm running PG 16.4.
- Jon
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why a bitmap scan in this case?
@ 2024-12-19 18:38 Greg Sabino Mullane <[email protected]>
parent: Jon Zeppieri <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Greg Sabino Mullane @ 2024-12-19 18:38 UTC (permalink / raw)
To: Jon Zeppieri <[email protected]>; +Cc: [email protected]
>
> Why wouldn't it do an index (or, really, an index only) scan in this case
Well, it did do an index scan (and a bitmap scan is a pretty good solution
here), but as to why no indexonly scan, there is probably not enough
assurance that it won't have to hit the heap heavily anyway. Try doing a
SET enable_bitmapscan=0; and re-run with EXPLAIN ANALYZE. If you see a
large number of "Heap Fetches", that could be why. Vacuum the table and try
again after doing SET enable_bitmapscan=1;
Cheers,
Greg
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why a bitmap scan in this case?
@ 2024-12-19 19:09 Jon Zeppieri <[email protected]>
parent: Greg Sabino Mullane <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Jon Zeppieri @ 2024-12-19 19:09 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: [email protected]
On Thu, Dec 19, 2024 at 1:39 PM Greg Sabino Mullane <[email protected]> wrote:
>>
>> Why wouldn't it do an index (or, really, an index only) scan in this case
>
>
> Well, it did do an index scan (and a bitmap scan is a pretty good solution here), but as to why no indexonly scan, there is probably not enough assurance that it won't have to hit the heap heavily anyway. Try doing a SET enable_bitmapscan=0; and re-run with EXPLAIN ANALYZE. If you see a large number of "Heap Fetches", that could be why. Vacuum the table and try again after doing SET enable_bitmapscan=1;
>
The table is freshly vacuumed. If I disable bitmap scans, it will do
an index only scan, which performs better. For the bitmap heap scan,
it says "Heap Blocks: exact=27393," whereas for the index only scan,
it's "Heap Fetches: 27701."
The row estimate is not good. The query estimates 317919 rows but
there are only 27701. There is some correlation here; if end_on is
null, start_on is a lot more likely to be recent, so maybe extended
statistics would be useful here.
- Jon
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why a bitmap scan in this case?
@ 2024-12-19 19:19 Jon Zeppieri <[email protected]>
parent: Jon Zeppieri <[email protected]>
1 sibling, 0 replies; 7+ messages in thread
From: Jon Zeppieri @ 2024-12-19 19:19 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: [email protected]
On Thu, Dec 19, 2024 at 2:09 PM Jon Zeppieri <[email protected]> wrote:
>
> The row estimate is not good. The query estimates 317919 rows but
> there are only 27701. There is some correlation here; if end_on is
> null, start_on is a lot more likely to be recent, so maybe extended
> statistics would be useful here.
>
Though, given that the index only contains rows where end_on is null,
it seems odd that the planner would estimate more rows than are
present in the index. That said, I have no idea whether the planner
uses that sort of information. -J
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why a bitmap scan in this case?
@ 2024-12-20 08:16 Frédéric Yhuel <[email protected]>
parent: Jon Zeppieri <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Frédéric Yhuel @ 2024-12-20 08:16 UTC (permalink / raw)
To: Jon Zeppieri <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected]
On 12/19/24 20:09, Jon Zeppieri wrote:
> The table is freshly vacuumed. If I disable bitmap scans, it will do
> an index only scan, which performs better. For the bitmap heap scan,
> it says "Heap Blocks: exact=27393," whereas for the index only scan,
> it's "Heap Fetches: 27701."
So you have 100% heap fetches. Are you sure that your table is freshly
vacuumed? Please note that VACUUM FULL doesn't create the visibility
map, so you still have to run a plain VACUUM for this.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why a bitmap scan in this case?
@ 2024-12-20 09:57 Frédéric Yhuel <[email protected]>
parent: Frédéric Yhuel <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Frédéric Yhuel @ 2024-12-20 09:57 UTC (permalink / raw)
To: Jon Zeppieri <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected]
On 12/20/24 09:16, Frédéric Yhuel wrote:
>
>
> On 12/19/24 20:09, Jon Zeppieri wrote:
>> The table is freshly vacuumed. If I disable bitmap scans, it will do
>> an index only scan, which performs better. For the bitmap heap scan,
>> it says "Heap Blocks: exact=27393," whereas for the index only scan,
>> it's "Heap Fetches: 27701."
>
> So you have 100% heap fetches. Are you sure that your table is freshly
> vacuumed? Please note that VACUUM FULL doesn't create the visibility
> map, so you still have to run a plain VACUUM for this.
>
>
And if you still have heap fetches, you could try VACUUM (INDEX_CLEANUP
TRUE), so that it doesn't bypass index vacuuming.
(In case the pages that contain theses 27701 tuples amount to less than
2% of the total number of pages of the table).
Nice explanation here:
https://www.cybertec-postgresql.com/en/making-the-postgresql-visibility-map-visible/#vacuum-doesnt-u...
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Why a bitmap scan in this case?
@ 2024-12-20 21:01 Jon Zeppieri <[email protected]>
parent: Frédéric Yhuel <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Jon Zeppieri @ 2024-12-20 21:01 UTC (permalink / raw)
To: Frédéric Yhuel <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; [email protected]
On Fri, Dec 20, 2024 at 4:57 AM Frédéric Yhuel
<[email protected]> wrote:
>
>
>
> On 12/20/24 09:16, Frédéric Yhuel wrote:
> >
> >
> > On 12/19/24 20:09, Jon Zeppieri wrote:
> >> The table is freshly vacuumed. If I disable bitmap scans, it will do
> >> an index only scan, which performs better. For the bitmap heap scan,
> >> it says "Heap Blocks: exact=27393," whereas for the index only scan,
> >> it's "Heap Fetches: 27701."
> >
> > So you have 100% heap fetches. Are you sure that your table is freshly
> > vacuumed? Please note that VACUUM FULL doesn't create the visibility
> > map, so you still have to run a plain VACUUM for this.
Ah, thanks -- I didn't know that. -J
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-12-20 21:01 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-19 17:52 Why a bitmap scan in this case? Jon Zeppieri <[email protected]>
2024-12-19 18:38 ` Greg Sabino Mullane <[email protected]>
2024-12-19 19:09 ` Jon Zeppieri <[email protected]>
2024-12-19 19:19 ` Jon Zeppieri <[email protected]>
2024-12-20 08:16 ` Frédéric Yhuel <[email protected]>
2024-12-20 09:57 ` Frédéric Yhuel <[email protected]>
2024-12-20 21:01 ` Jon Zeppieri <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox