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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gregory Stark | 2007-12-19 01:49:50 | Re: BUG #3826: Very Slow Execution of examplequery (wrong plan?) |
Previous Message | Gregory Stark | 2007-12-18 20:21:50 | Re: BUG #3826: Very Slow Execution of examplequery (wrong plan?) |