Re: Bitmap heap scan performance

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Bitmap heap scan performance
Дата
Msg-id CAMkU=1zcGQ0MNsbnP+8vqOqfz-h2dvKXSw=EVysS+u5uCsppFw@mail.gmail.com
обсуждение исходный текст
Ответ на Bitmap heap scan performance  (Rob Emery <re-pgsql@codeweavers.net>)
Ответы Re: Bitmap heap scan performance
Список pgsql-performance
On Fri, Aug 9, 2019 at 4:42 AM Rob Emery <re-pgsql@codeweavers.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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Rob Emery
Дата:
Сообщение: Bitmap heap scan performance
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Postgres not using correct indices for views.