Обсуждение: [SQL] How could I improve a query with a function in the join clause?

Поиск
Список
Период
Сортировка

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

От
Daniel Franco
Дата:
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.

Re: [SQL] How could I improve a query with a function in the join clause?

От
Andreas Kretschmer
Дата:
On 14 September 2017 01:55:48 GMT+02:00, Daniel Franco <dpinheirofranco@gmail.com> wrote:
>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.
>
>
>
>What is the best option for optimizing this type of query?
>
>Thanks in advance.


Upgrade to an actual Version.


Andreas


--
2ndQuadrant - The PostgreSQL Support Company


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql