pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedcorrelated exists with join is slow.
4+ messages / 3 participants
[nested] [flat]
* correlated exists with join is slow.
@ 2012-06-18 13:47 Віталій Тимчишин <tivv00@gmail.com>
2012-06-18 14:52 ` Re: correlated exists with join is slow. Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Віталій Тимчишин @ 2012-06-18 13:47 UTC (permalink / raw)
To: pgsql-performance
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;
-- http://explain.depesz.com/s/Rvw
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;
-- http://explain.depesz.com/s/fVHw
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;
-- http://explain.depesz.com/s/HgN
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;
-- http://explain.depesz.com/s/mLA
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;
-- http://explain.depesz.com/s/LYu
rollback;
--
Best regards,
Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: correlated exists with join is slow.
2012-06-18 13:47 correlated exists with join is slow. Віталій Тимчишин <tivv00@gmail.com>
@ 2012-06-18 14:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-06-18 14:58 ` Re: correlated exists with join is slow. Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-18 15:02 ` Re: correlated exists with join is slow. Віталій Тимчишин <tivv00@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Tom Lane @ 2012-06-18 14:52 UTC (permalink / raw)
To: Віталій Тимчишин <tivv00@gmail.com>; +Cc: pgsql-performance
=?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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: correlated exists with join is slow.
2012-06-18 13:47 correlated exists with join is slow. Віталій Тимчишин <tivv00@gmail.com>
2012-06-18 14:52 ` Re: correlated exists with join is slow. Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-06-18 14:58 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 4+ messages in thread
From: Kevin Grittner @ 2012-06-18 14:58 UTC (permalink / raw)
To: tivv00@gmail.com; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: correlated exists with join is slow.
2012-06-18 13:47 correlated exists with join is slow. Віталій Тимчишин <tivv00@gmail.com>
2012-06-18 14:52 ` Re: correlated exists with join is slow. Tom Lane <tgl@sss.pgh.pa.us>
@ 2012-06-18 15:02 ` Віталій Тимчишин <tivv00@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Віталій Тимчишин @ 2012-06-18 15:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Glad to hear postgresql becomes better and better :)
2012/6/18 Tom Lane <tgl@sss.pgh.pa.us>
> =?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
>
--
Best regards,
Vitalii Tymchyshyn
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-06-18 15:02 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-18 13:47 correlated exists with join is slow. Віталій Тимчишин <tivv00@gmail.com>
2012-06-18 14:52 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-06-18 14:58 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-18 15:02 ` Віталій Тимчишин <tivv00@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox