Re: IN vs EXIIST

On Wed, 2002-09-18 at 21:40, Jean-Christian Imbeault wrote:
> select distinct key1 from A where id not it
>    (select distinct key1 from A where x='false');
>
> I've seen many posts saying that using IN is not optimal and replacing
> it with EXISTS is much better. I've read the only docs but I can't
> understand the difference between the two or how to convert.

I just did this today for a query and got a 200x speedup when converting
from IN to EXISTS.  It's dependent on your specific query exactly how to
do the conversion, but generally it's just a question of moving a field
from the "outside" to the "inside" of the subselect.  For me,

  SELECT * FROM inventory WHERE itemid IN (
    SELECT itemid FROM osinfo WHERE ostype = 'linux' );

became

  SELECT * FROM inventory WHERE EXISTS (
     SELECT itemid FROM osinfo
     WHERE ostype = 'linux' and inventory.itemid = osinfo.itemid);

Same results, same database; first query took 8 sec, second took 39
msec.

YMMV
b.g.








>
> Can someone point me to some other docs or explain to me how to convert?
> Or is my table schema wrong?
>
> Thanks!
>
> Jc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)