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 <jeff(dot)janes(at)gmail(dot)com> wrote:
> On Fri, Aug 9, 2019 at 4:42 AM Rob Emery <re-pgsql(at)codeweavers(dot)net> 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: robertemery(at)codeweavers(dot)net | 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: contactus(at)codeweavers(dot)net
<mailto:contactus(at)codeweavers(dot)net>
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>