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;
