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

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