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.janes@xxxxxxxxx> wrote: > On Fri, Aug 9, 2019 at 4:42 AM Rob Emery <re-pgsql@xxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxx | 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@xxxxxxxxxxxxxxx <mailto:contactus@xxxxxxxxxxxxxxx> 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>
