Обсуждение: correlated exists with join is slow.

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

correlated exists with join is slow.

От
Віталій Тимчишин
Дата:
Hello.

Today I've found a query that I thought will be fast turned out to be slow. The problem is correlated exists with join - it does not want to make correlated nested loop to make exists check.
Even if I force it to use nested loop, it materialized join uncorrelated and then filters it. It's OK when exists does not have join. Also good old left join where X=null works fast.
Note that I could see same problem for both exists and not exists.
Below is test case (tested on 9.1.4) with explains.


create temporary table o(o_val,c_val) as select v, v/2 from generate_series(1,1000000) v;
create temporary table i(o_ref, l_ref) as select generate_series(1,1000000), generate_series(1,10);
create temporary table l(l_val, l_name) as select v, 'n_' || v from generate_series(1,10) v;
create index o_1 on o(o_val);
create index o_2 on o(c_val);
create index i_1 on i(o_ref);
create index i_2 on i(l_ref);
create index l_1 on l(l_val);
create index l_2 on l(l_name);
analyze o;
analyze i;
analyze l;
explain analyze select 1 from o where not exists (select 1 from i join l on l_ref = l_val where l_name='n_2' and o_ref=o_val) and c_val=33;
explain analyze select 1 from o where not exists (select 1 from i join l on l_ref = l_val where l_val=2 and o_ref=o_val) and c_val=33;
explain analyze select 1 from o where not exists (select 1 from i where l_ref=2 and o_ref=o_val) and c_val=33;
explain analyze select 1 from o left join i on o_ref=o_val left join l on l_ref = l_val and l_name='n_2' where o_ref is null and c_val=33;
set enable_hashjoin=false;
explain analyze select 1 from o where not exists (select 1 from i join l on l_ref = l_val where l_name='n_2' and o_ref=o_val) and c_val=33;
rollback;

--
Best regards,
 Vitalii Tymchyshyn

Re: correlated exists with join is slow.

От
Tom Lane
Дата:
=?KOI8-U?B?96bUwcymyiD0yc3eydvJzg==?= <tivv00@gmail.com> writes:
> Today I've found a query that I thought will be fast turned out to be slow.
> The problem is correlated exists with join - it does not want to make
> correlated nested loop to make exists check.

9.2 will make this all better.  These are exactly the type of case where
you need the "parameterized path" stuff.

            regards, tom lane

Re: correlated exists with join is slow.

От
"Kevin Grittner"
Дата:
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 9.2 will make this all better.  These are exactly the type of case
> where you need the "parameterized path" stuff.

Yeah, with HEAD on my workstation all of these queries run in less
than 0.1 ms.  On older versions, I'm seeing times like 100 ms to 150
ms for the slow cases.  So in this case, parameterized paths allow
an improvement of more than three orders of magnitude.  :-)

-Kevin

Re: correlated exists with join is slow.

От
Віталій Тимчишин
Дата:
Glad to hear postgresql becomes better and better :)

2012/6/18 Tom Lane <tgl@sss.pgh.pa.us>
Віталій Тимчишин <tivv00@gmail.com> writes:
> Today I've found a query that I thought will be fast turned out to be slow.
> The problem is correlated exists with join - it does not want to make
> correlated nested loop to make exists check.

9.2 will make this all better.  These are exactly the type of case where
you need the "parameterized path" stuff.

                       regards, tom lane

-- 
Best regards,
 Vitalii Tymchyshyn