On Mon, Oct 30, 2023 at 9:28 AM Loles <lolesft@xxxxxxxxx> wrote:
Hi!A question about query optimization.
We have a simple query with several tables joined.
We have seen performance down when we use WHERE UPPER(name) LIKE UPPER('%Alice%') in the condition.
If we compare with WHERE UPPER(name) = UPPER('Alice') it runs very fast.
We have tried an index on the name field, an UPPER(name) _expression_ index, a GIN index, etc. but nothing seems to work.
A gin_trgm_ops _expression_ index on UPPER(name) should work. We can't tell if that is one of the things you tried.
But it would probably be better to use ILIKE and omit the UPPER calls, in which case a gin_trgm_ops index on the column itself should work.
Cheers,
Jeff
