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(at)svana(dot)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.