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
