Skip site navigation (1) Skip section navigation (2)

Re: BUG #3826: Very Slow Execution of examplequery (wrong plan?)

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: "Alexander Steffens" <mail(at)a-st(dot)de>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3826: Very Slow Execution of examplequery (wrong plan?)
Date: 2007-12-18 22:18:50
Message-ID: 5733.1198016330@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> insert into t1
>> select distinct (t1.a + t2.a)*2
>> from t1,t2
>> where not exists (
>> select * from t1  tt where tt.a = (t1.a + t2.a)*2
>> ) 

> What plan does MS-SQL use to complete this? I wonder whether it's producing
> the same answer Postgres is.

AFAICS there is just no very good way to execute a query with such
little structure.  You pretty much have to form the cartesian product
of t1 x t2 and then do a rather expensive subquery probe for each row.
There isn't even an index on tt.a to help :-(

You could probably make it slightly less bad by changing the query to

select distinct (t1.a + t2.a)*2
from t1,t2
where (t1.a + t2.a)*2 not in (
 select tt.a from t1 tt
);

which would enable PG to use a hashed-subplan implementation of the NOT
IN probe.  This transformation is not legal in general --- it will
produce different answers if t1.a or t2.a could be NULL --- but if you
know you don't care about that then it's OK.

It's possible that MS-SQL is doing something analogous to the
hashed-subplan approach (hopefully with suitable tweaking for the NULL
case) but even then it's hard to see how it could take only 9 sec.
The cartesian product is too big.

BTW, increasing work_mem should help; it looks to me like a sizable
amount of time goes into the sort for the final DISTINCT.

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Gregory StarkDate: 2007-12-19 01:49:50
Subject: Re: BUG #3826: Very Slow Execution of examplequery (wrong plan?)
Previous:From: Gregory StarkDate: 2007-12-18 20:21:50
Subject: Re: BUG #3826: Very Slow Execution of examplequery (wrong plan?)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group