Re: Highly obscure and erratic

On Wed, Jun 19, 2002 at 04:33:10AM +0530, Varun Kacholia wrote:
>
> > What does explain show for the exists version?
>
> suryadb=# explain select * from dbmedia where EXISTS (select ID from
> wdmedia where word='whatever' AND dbmedia.id=id ) LIMIT 200;

how does this differ from:

select *
from dbmedia
where dbmedia.id=wdmedia.id
and wdmedia.word='whatever'
limit 200;

> NOTICE:  QUERY PLAN:
>
> Limit  (cost=0.00..1006732.42 rows=200 width=76)
>   ->  Seq Scan on dbmedia  (cost=0.00..507810931.25 rows=100883 width=76)
>           SubPlan
>                     ->  Index Scan using wdkmedia on wdmedia  (cost=0.00..5033.63 rows=1 width=4)
>
>                     EXPLAIN
>
> still seq scan :((
> someone please fix this bug :(

I think it's called "pilot error". Your query asked to run the subquery for
each row in the outer query, so ofcourse you get a sequential scan. If what
you wanted was an index scan then you should rewrite it as a join (as above)
and use that.

If you can prove that your EXISTS statement is equivalent to the JOIN for
all different types of subqueries, perhaps it can be made automatic.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.