Re: Very Very Wierd

On Wed, Jun 19, 2002 at 01:24:28AM +0530, Varun Kacholia wrote:
>  hi ,
>    Now i think something has happened wrong to the db that after doing
>    vaccum analyze, it has started behaving very very weirdly.
>
>    suryadb=# explain SELECT * FROM dbmedia WHERE ID IN ((SELECT id FROM wdmedia WHERE word = 'boyzone') )  LIMIT 200;
>    NOTICE:  QUERY PLAN:
>    Limit  (cost=0.00..1005445.27 rows=200 width=76)
>      ->  Seq Scan on dbmedia  (cost=0.00..507161673.46 rows=100883 width=76)
>          ^^^^^^^^^^^
>              SubPlan
>                        ->  Materialize  (cost=5027.19..5027.19 rows=2575 width=4)
>                                        ->  Index Scan using wdkmedia on wdmedia  (cost=0.00..5027.19 rows=2575
width=4)
>
>  why the hell cant it recognise that ID is a primary field and it has
>  just to do an index lookup? and also seeing at the time which is taken
>  to execute the query i am damn sure that entire table scan is done.
>  it takes abt 30 secs to finish it (Athlon XP 1.7 gigs, 128 MB DDR).
>  And i think that this is terrible.Also it is not that the word is too
>  frequent that it chooses to do a sequential scan than an indexed one .
>  Should i build up an index also on ID so that it recognises it?
>  or is there a flaw in postgresql that queries with 'IN' are not
>  looked up from index but sequentially scanned?

Look up the FAQ. Use EXISTS, not IN. No-one has shown to satisfaction when
an IN can be converted to the equivalent EXISTS.

--
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.