Thread: Move constant evaluation to inside IN subselect

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