Re: Avoid full GIN index scan when possible - Mailing list pgsql-hackers

From Julien Rouhaud
Subject Re: Avoid full GIN index scan when possible
Date
Msg-id CAOBaU_ZCg4955_HGcQZdn44wtTQRpiHkLgkVsxqQ5cfymDL-XQ@mail.gmail.com
Whole thread Raw
In response to Avoid full GIN index scan when possible  (Julien Rouhaud <rjuju123@gmail.com>)
Responses Re: Avoid full GIN index scan when possible
List pgsql-hackers
On Sun, Mar 24, 2019 at 11:52 AM Julien Rouhaud <rjuju123@gmail.com> wrote:
>
> Marc (in Cc) reported me a problematic query using a GIN index hit in
> production.  The issue is that even if an GIN opclass says that the
> index can be used for an operator, it's still possible that some
> values aren't really compatible and requires a full index scan.
>
> One simple example is with a GIN pg_trgm index (but other opclasses
> have similar restrictions) , doing a LIKE with wildcard on both side,
> where the pattern is shorter than a trigram, e.g. col LIKE '%a%'.  So,
> a where clause of the form:
>
> WHERE col LIKE '%verylongpattern%' AND col LIKE '%a%'
>
> is much more expensive than
>
> WHERE col LKE '%verylongpattern%'
>
> While there's nothing to do if the unhandled const is the only
> predicate, if there are multiple AND-ed predicates and at least one of
> them doesn't require a full index scan, we can avoid it.
>
> Attached patch tries to fix the issue by detecting such cases and
> dropping the unhandled quals in the BitmapIndexScan, letting the
> recheck in BitmapHeapScan do the proper filtering.  I'm not happy to
> call the extractQuery support functions an additional time, but i
> didn't find a cleaner way.  This is of course intended for pg13.

Patch doesn't apply anymore (thanks cfbot).  Rebased patch attached.

Attachment

pgsql-hackers by date:

Previous
From: Adam Berlin
Date:
Subject: Re: C testing for Postgres
Next
From: Daniel Gustafsson
Date:
Subject: Superfluous libpq-be.h include in GSSAPI code