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

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')
Дата
Msg-id CAMkU=1ykvx7x-ezab-hukCM9EXOa0X9DdaX+GFdU+McKhGAY3w@mail.gmail.com
обсуждение исходный текст
Ответ на Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')  (Loles <lolesft@gmail.com>)
Список pgsql-admin
Дерево обсуждения
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 по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')
Следующее
От: Dan Smith
Дата:
Сообщение: Re: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')