Move constant evaluation to inside IN subselect

From: Cesar Eduardo Barros <cesarb(at)elnetcorp(dot)com(dot)br>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Move constant evaluation to inside IN subselect
Date: 2002-07-06 16:10:36
Message-ID: 20020706161036.GB26802@cerberus.elnet.grupomk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


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(at)elnetcorp(dot)com(dot)br

Browse pgsql-bugs by date

  From Date Subject
Next Message Cesar Eduardo Barros 2002-07-06 17:04:26 Re: INSERT .. SELECT should redo SELECT if a duplicate key is found
Previous Message Cesar Eduardo Barros 2002-07-06 15:18:56 INSERT .. SELECT should redo SELECT if a duplicate key is found