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;
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;
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;
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;
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;
rollback;
Best regards,
Vitalii Tymchyshyn
