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
