In the script below, the presence of an IN clause forces the internal components of the UNION ALL clause to fully compute even though they are fully optimizable. = ANY doesn't have this issue, so I wonder if there is any opportunity to convert the 'slow' variant (see below) to the 'fast' variant. thank you!
merlin drop table a cascade; drop table b cascade; drop table c cascade; create table a (a_id int primary key); create table b (b_id int primary key, a_id int references a); create table c (c_id int primary key, b_id int references b); insert into a select s from generate_series(1, 50000) s; insert into b select s, (s % 50000 ) + 1 from generate_series(1, 100000) s; insert into c select s, (s % 100000 ) + 1 from generate_series(1, 1000000) s; create index on b (a_id, b_id); create index on c (b_id, c_id); analyze a; analyze b; analyze c; create temp table d (a_id int); insert into d values (99); insert into d values (999); insert into d values (9999); analyze d; create or replace view v as select * from a join b using(a_id) join c using(b_id) union all select * from a join b using(a_id) join c using(b_id); explain analyze select * from v where a_id in (select a_id from d); -- this is slow explain analyze select * from v where a_id = any(array(select a_id from d)); -- this is fast
