Avoid full GIN index scan when possible
От | Julien Rouhaud |
---|---|
Тема | Avoid full GIN index scan when possible |
Дата | |
Msg-id | CAOBaU_YGP5-BEt5Cc0=zMve92vocPzD+XiZgiZs1kjY0cj=XBg@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Avoid full GIN index scan when possible
|
Список | 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.
Вложения
В списке pgsql-hackers по дате отправления: