Re: Avoid full GIN index scan when possible

Hi!

On Sat, Jun 29, 2019 at 1:52 AM Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru> wrote:
> We have a similar solution for this problem. The idea is to avoid full index
> scan inside GIN itself when we have some GIN entries, and forcibly recheck
> all tuples if triconsistent() returns GIN_MAYBE for the keys that emitted no
> GIN entries.
>
> The attached patch in its current shape contain at least two ugly places:
>
> 1. We still need to initialize empty scan key to call triconsistent(), but
> then we have to remove it from the list of scan keys. Simple refactoring
> of ginFillScanKey() can be helpful here.
>
> 2. We need to replace GIN_SEARCH_MODE_EVERYTHING with GIN_SEARCH_MODE_ALL
> if there are no GIN entries and some key requested GIN_SEARCH_MODE_ALL
> because we need to skip NULLs in GIN_SEARCH_MODE_ALL. Simplest example here
> is "array @> '{}'": triconsistent() returns GIN_TRUE, recheck is not forced,
> and GIN_SEARCH_MODE_EVERYTHING returns NULLs that are not rechecked. Maybe
> it would be better to introduce new GIN_SEARCH_MODE_EVERYTHING_NON_NULL.

Thank you for publishing this!

What would happen when two-columns index have GIN_SEARCH_MODE_DEFAULT
scan on first column and GIN_SEARCH_MODE_ALL on second? I think even
if triconsistent() for second column returns GIN_TRUE, we still need
to recheck to verify second columns is not NULL.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company