Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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



pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group