Re: IN vs EXISTS

> -----Original Message-----
>
> Hi all,
>
> I have been using IN clause almost exclusively until recently I tried
> to use EXISTS and gained significant performance increase without
> changing/creating any indexes:
>
> SELECT ... FROM a WHERE a.ref IN (SELECT b.id WHERE ...)
>
> vs
>
> SELECT ... FROM a WHERE EXISTS (SELECT 1 FROM b WHERE a.ref=b.id ...)
>
> Performance is at least few times better when EXISTS is used. Is it
> just PostgreSQL specific?
>

IN should produce a different query plan than EXISTS. (You can run
"explain analyze" on your queries, to see the different plans).
Which one is faster depends on your data, and on your server. Also,
what's faster on one dbms my be different than another. I've found
that postgresql is usually slower than other databases for IN ()
queries, but handles EXISTS and inner joins (a third way of writing
your queries above) quite quickly.

SELECT a.foo
FROM a
INNER JOIN b on a.ref=b.id