The following bug has been logged online:
Bug reference: 3826
Logged by: Alexander Steffens
Email address: mail@a-st.de
PostgreSQL version: 8.3b4
Operating system: Win2003R2x64
Description: Very Slow Execution of examplequery (wrong plan?)
Details:
Hello, I have found an Query (with data)
that need to execute on MS-SQL 2005 < 9sec,
on Postgresql I will stop it now after more than 30 mins:
create table t1 (a int);
create table t2 (a int);
insert into t1 select 1; --for t-sql compat
insert into t1 select 2;insert into t1 select 3;
insert into t2 select 1;
insert into t2 select 2;
insert into t2 select 5;
--execute 8 times QUERY A
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
)
--execute 1 times
insert into t2
select distinct (t1.a + t2.a)*3
from t1,t2
where not exists (
select * from t2 tt where tt.a = (t1.a + t2.a)*3
)
--
--data now t1: 1642 t2: 3301
--
--now again QUERY A
--will need much too much time:
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
)
Alex.