Postgres Pro
Downloads
Home   >   mailing lists

Re: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%') - Mailing list pgsql-admin

From Jeff Janes
Subject Re: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')
Date
Msg-id CAMkU=1ykvx7x-ezab-hukCM9EXOa0X9DdaX+GFdU+McKhGAY3w@mail.gmail.com
Whole thread Raw
In response to Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')  (Loles <lolesft@gmail.com>)
List pgsql-admin
Tree view
On Mon, Oct 30, 2023 at 9:28 AM Loles <lolesft@gmail.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

pgsql-admin by date:

Previous
From: Tom Lane
Date:
Subject: Re: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')
Next
From: Dan Smith
Date:
Subject: Re: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')