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

Поиск
Список
Период
Сортировка
От Daniel Franco
Тема [SQL] How could I improve a query with a function in the join clause?
Дата
Msg-id CAOzZ813cp=_qe=ah4j5oV70ZmiKL229Wsh_+KxO1hm=eu9Y-4w@mail.gmail.com
обсуждение исходный текст
Ответы Re: [SQL] How could I improve a query with a function in the join clause?  (Andreas Kretschmer <andreas@a-kretschmer.de>)
Список 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.

В списке pgsql-sql по дате отправления:

Предыдущее
От: Ertan Küçükoğlu
Дата:
Сообщение: Re: [SQL] FirebirdSQL to PostgreSQL
Следующее
От: Andreas Kretschmer
Дата:
Сообщение: Re: [SQL] How could I improve a query with a function in the join clause?