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
