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

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(at)gmail(dot)com>
Envoyé : lundi 30 octobre 2023 14:28
À : pgsql-admin(at)postgresql(dot)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!!