How could I improve a query with a function in the join clause?

From: Daniel Franco <dpinheirofranco(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: How could I improve a query with a function in the join clause?
Date: 2017-09-13 23:55:48
Message-ID: CAOzZ813cp=_qe=ah4j5oV70ZmiKL229Wsh_+KxO1hm=eu9Y-4w@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Andreas Kretschmer 2017-09-14 01:40:50 Re: How could I improve a query with a function in the join clause?
Previous Message Ertan Küçükoğlu 2017-09-12 20:22:51 Re: FirebirdSQL to PostgreSQL