Avoid full GIN index scan when possible

From: Julien Rouhaud <rjuju123(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Marc Cousin <cousinmarc(at)gmail(dot)com>
Subject: Avoid full GIN index scan when possible
Date: 2019-03-24 10:52:52
Message-ID: CAOBaU_YGP5-BEt5Cc0=zMve92vocPzD+XiZgiZs1kjY0cj=XBg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Attachment Content-Type Size
avoid_gin_fullscan-v1.diff text/x-patch 7.0 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2019-03-24 10:54:53 Assert failure when validating foreign keys
Previous Message Julien Rouhaud 2019-03-24 10:24:50 Re: Planning counters in pg_stat_statements (using pgss_store)