Re: Why a bitmap scan in this case? - Mailing list pgsql-performance

From Jon Zeppieri
Subject Re: Why a bitmap scan in this case?
Date
Msg-id CAKfDxxw-=_naXS-ypU9qWj8eNbAEJvGCy5ZxHA5HW=+M-baDcw@mail.gmail.com
Whole thread Raw
In response to Re: Why a bitmap scan in this case?  (Greg Sabino Mullane <htamfids@gmail.com>)
Responses Re: Why a bitmap scan in this case?
Re: Why a bitmap scan in this case?
List pgsql-performance
On Thu, Dec 19, 2024 at 1:39 PM Greg Sabino Mullane <htamfids@gmail.com> 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,
thereis 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.
Vacuumthe 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



pgsql-performance by date:

Previous
From: Greg Sabino Mullane
Date:
Subject: Re: Why a bitmap scan in this case?
Next
From: Jon Zeppieri
Date:
Subject: Re: Why a bitmap scan in this case?