Postgres Pro
Downloads
Home   >   mailing lists

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

From HALAT-PRUVOT SYLVIE
Subject RE: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')
Date
Msg-id 1c16db2fbba145dd900f0fe14849bf99@region-academique-occitanie.fr
Whole thread Raw
In response to Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')  (Loles <lolesft@gmail.com>)
List pgsql-admin
Tree view

Hi,

 

You can try with extension pg_tgrm to create gin index to use LIKE with ‘%expression%’

 

https://www.postgresql.org/docs/current/pgtrgm.html

https://mazeez.dev/posts/pg-trgm-similarity-search-and-fast-like

 

Sylvie Halat

De : Loles <lolesft@gmail.com>
Envoyé : lundi 30 octobre 2023 14:28
À : pgsql-admin@postgresql.org
Objet : Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')

 

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.

We need to optimize the condition WHERE UPPER(name) LIKE UPPER('%p_name%)

Any ideas?

 

Thanks!!

pgsql-admin by date:

Previous
From: SOzcn
Date:
Subject: Re: On Replica - History issue
Next
From: Ron
Date:
Subject: Re: AUTOVACUUM