pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimized WHERE UPPER(name) LIKE UPPER('%p_name%')
5+ messages / 5 participants
[nested] [flat]
* Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')
@ 2023-10-30 13:28 Loles <lolesft@gmail.com>
0 siblings, 4 replies; 5+ messages in thread
From: Loles @ 2023-10-30 13:28 UTC (permalink / raw)
To: pgsql-admin
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!!
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')
@ 2023-10-30 13:39 HALAT-PRUVOT SYLVIE <sylvie.halat-pruvot@region-academique-occitanie.fr>
parent: Loles <lolesft@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: HALAT-PRUVOT SYLVIE @ 2023-10-30 13:39 UTC (permalink / raw)
To: Loles <lolesft@gmail.com>; pgsql-admin
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!!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')
@ 2023-10-30 14:08 Tom Lane <tgl@sss.pgh.pa.us>
parent: Loles <lolesft@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2023-10-30 14:08 UTC (permalink / raw)
To: Loles <lolesft@gmail.com>; +Cc: pgsql-admin
Loles <lolesft@gmail.com> writes:
> 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.
Presumably, that works well because you have a btree index on UPPER(name).
But btree can't optimize a pattern with a leading '%' --- see
https://www.postgresql.org/docs/current/indexes-types.html#INDEXES-TYPES-BTREE
You could possibly use a pg_trgm index instead, see
https://www.postgresql.org/docs/current/pgtrgm.html
The performance properties will be somewhat different from btree,
but it might get the job done.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')
@ 2023-10-30 14:11 Jeff Janes <jeff.janes@gmail.com>
parent: Loles <lolesft@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Jeff Janes @ 2023-10-30 14:11 UTC (permalink / raw)
To: Loles <lolesft@gmail.com>; +Cc: 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Optimized WHERE UPPER(name) LIKE UPPER('%p_name%')
@ 2023-10-30 14:29 Dan Smith <j.daniel.smith1@gmail.com>
parent: Loles <lolesft@gmail.com>
3 siblings, 0 replies; 5+ messages in thread
From: Dan Smith @ 2023-10-30 14:29 UTC (permalink / raw)
To: pgsql-admin
Hello!
You might want to check that query plan; functions in where and join
clauses can lead to running the function per row which will be a resource
intensive process. My guess is that you are touching an awful lot of data
you don't need in the query.
What I would normally recommend is to add a column on these tables (or to a
materialized view) to support the join where data is stored as you want to
use it (store the value of *UPPER(name)* vs running on demand).
Alternatively, you could test creating an index where the function is
already applied to see if the query planner would use it. Further, I doubt
the double wildcard like filter is ever going to be super efficient in the
query (regardless of gin index). These kinds of search operations are
seldom optimal and in my experience have a tendency to decrease in
performance with the volume of data. Acceptable performance will probably
depend on your specific use case and data.
Where the statement of direct equality can be supported by a b-tree index
and would certainly be optimized.
If you really need to support this exact functionality, you may want to
look in to the following features and extensions:
- https://www.postgresql.org/docs/current/pgtrgm.html
-
https://www.postgresql.org/docs/current/datatype-textsearch.html#DATATYPE-TSVECTOR
- https://www.postgresql.org/docs/current/textsearch.html
- https://www.postgresql.org/docs/current/functions-textsearch.html
- https://www.postgresql.org/docs/current/fuzzystrmatch.html
Hope this helps! I'm certain others will have great ideas and comments as
well.
Cheers!
Dan Smith
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-10-30 14:29 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-30 13:28 Optimized WHERE UPPER(name) LIKE UPPER('%p_name%') Loles <lolesft@gmail.com>
2023-10-30 13:39 ` HALAT-PRUVOT SYLVIE <sylvie.halat-pruvot@region-academique-occitanie.fr>
2023-10-30 14:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-30 14:11 ` Jeff Janes <jeff.janes@gmail.com>
2023-10-30 14:29 ` Dan Smith <j.daniel.smith1@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox