constant IN (SELECT col FROM ...)
and
EXISTS (SELECT col FROM ... WHERE col = constant)
are equivalent. Moving the constant to inside the subquery can make a
big difference.
teste=# create table teste (id integer primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
'teste_pkey' for table 'teste'
CREATE
teste=# explain select 15 in (select id from teste);
NOTICE: QUERY PLAN:
Result (cost=0.00..0.01 rows=1 width=0)
SubPlan
-> Seq Scan on teste (cost=0.00..20.00 rows=1000 width=4)
EXPLAIN
teste=# explain select exists (select 1 from teste where id = 15);
NOTICE: QUERY PLAN:
Result (cost=0.00..0.01 rows=1 width=0)
InitPlan
-> Index Scan using teste_pkey on teste (cost=0.00..4.82 rows=1
width=0)
EXPLAIN
The stats from the planer seem to be wrong; the seq scan won't return
the desired row immediately. This might be another bug.
The planner should convert
_non-null constant_ IN (SELECT _non-aggregate_ FROM ... WHERE ...)
into
EXISTS (SELECT 1 FROM ... WHERE (...) AND _non-aggregate_ = _non-null constant_)
--
Cesar Eduardo Barros
ElNet Hightech -- Administrador de Sistemas Unix
cesarb@elnetcorp.com.br
