IN vs EXISTS

From: askel <dummy666(at)mail(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: IN vs EXISTS
Date: 2008-06-07 03:58:26
Message-ID: a72b07b5-765b-4231-8846-fd44646b5ac8@w7g2000hsa.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alban Hertroys 2008-06-07 10:12:43 Re: strpos NOT doing what I'd expect
Previous Message Tom Lane 2008-06-07 02:06:27 Re: strpos NOT doing what I'd expect