Hi,
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.