Re: Bitmap heap scan performance

Aha!

That's a great hint, we had that set down to an obscenely low value
due to our max_connections setting being quite high. I've tweaked it
back up to 4MB for now and it's definitely had a marked improvement!

Many Thanks,
Rob

On 09/08/2019, Jeff Janes <[email protected]> wrote:
> On Fri, Aug 9, 2019 at 4:42 AM Rob Emery <[email protected]> wrote:
>
>
>>
>> It
>> seems to me like the Bitmap Heap Scan on proposal is the issue because
>> the recheck is throwing away enormous amounts of data.
>
>
> Have you tried increasing work_mem?  The probable reason for the recheck is
> that your bitmap overflows the allowed memory, and then switches
> from storing every tid to storing just the block numbers.  As indicated by
> the lossy part of "Heap Blocks: exact=3983 lossy=27989"
>
> The
>> has_been_anonymised flag on the proposal is effectively a soft-delete;
>> so I’ve tried adding something like :
>>
>> CREATE INDEX ON proposal.proposal (system_id, legacy_organisation_id,
>> reference)
>> WHERE has_been_anonymised = false;
>>
>> Which I was hoping would shrink the size of the index significantly
>>
>
> The partial index should be smaller, but when comparing to the index with
> "has_been_anonymised" as the leading column, it won't make a lot of
> difference.  You only have to scan a smaller part of the larger index, and
> the sizes of part of the index you have to scan in each case will be
> roughly comparable.
>
>
>> and encourage an index scan rather than bitmap, however it didn’t have
>> that effect.
>
>
> To encourage index scans over bitmap scans, you can increase
> effective_cache_size.  Or to really force the issue, you can "set
> enable_bitmapscan=off" but that is something you would usually do locally
> for experimental purposes, not do it in production's config settings.
>
> Cheers,
>
> Jeff
>


-- 
Robert Emery
Infrastructure Director

E: [email protected] | T: 01785 711633 | W: www.codeweavers.net

-- 
 <https://codeweavers.net>


A big Get Focused ‘thank you’ 
<https://codeweavers.net/company-blog/a-big-get-focused-thank-you>
Why you 
should partner with an Agile company 
<https://codeweavers.net/company-blog/why-you-should-partner-with-an-agile-company>


*
*
*Phone:* 0800 021 0888   Email: [email protected] 
<mailto:[email protected]>
Codeweavers Ltd | Barn 4 | Dunston 
Business Village | Dunston | ST18 9AB
Registered in England and Wales No. 
04092394 | VAT registration no. 974 9705 63 



 
<https://twitter.com/Codeweavers_Ltd>  
<https://www.facebook.com/Codeweavers.Ltd/>  
<https://www.linkedin.com/company/codeweavers-limited>