Re: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')

On Mon, Oct 30, 2023 at 9:28 AM Loles <lolesft(at)gmail(dot)com> 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

>