On Sun, Mar 24, 2019 at 11:52 AM Julien Rouhaud <rjuju123(at)gmail(dot)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.