IN vs EXISTS

askel <dummy666@xxxxxxx> · Fri, 6 Jun 2008 20:58:26 -0700 (PDT)

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?

Cheers