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@xxxxxxxxx>
Envoyé : lundi 30 octobre 2023 14:28
À : pgsql-admin@xxxxxxxxxxxxxx
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%)
