I'm trying to improve the performance of a query in PostgreSQL with lots of left joins that uses the lower function in the join condition. The database version is 8.3.
For simplification purposes, the following query has a similar structure of what I'm having issues with:
SELECT t1.c
FROM t1
LEFT JOIN t2
ON lower(t1.a)=lower(t2.b);
The t1 table is very huge (more than 10GB) and the t2 table is not as big as t1.
I created an expression index on both of these columns (a and b) to see if it would be possible to pre-calculate the function's results before the join operation, but the indexes were not used. Apparently the indexes cannot be used during joins.
What is the best option for optimizing this type of query?
Thanks in advance.
