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.