Subject: in() VS exists()
From: Julien <jcigar@ulb.ac.be>
To: pgsql-sql <pgsql-sql@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Date: Thu, 13 Mar 2008 15:47:24 +0100
Message-Id: <1205419644.3240.19.camel@frodon.be-bif.ulb.ac.be>
Mime-Version: 1.0
Content-Transfer-Encoding: 8bit

Hello,

Does anyone has an idea why sometimes:
- select ... where ... in (select ...)
is faster than :
- select ... where ... exists(select ...)
and sometimes it's the opposite ?

I had such a situation, I've pasted the queries on:
http://rafb.net/p/KXNZ6892.html and http://rafb.net/p/jvo5DO38.html

It's running PostgreSQL 8.1 with an effective_cache_size of 30000.

specimens.id is the primary key and there are indexes on
sequences(specimen_id) and specimen_measurements(specimen_id)

Is there a general "rule" to know when to use the in() version and when
to use the exists() version ? Is it true to say that the exists()
version is more scalable (with many rows) than the in() version (from
the little tests I made it seems the case) ?

Thanks,
Julien

-- 
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Université Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
Bâtiment NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entrée ULB 2
B-1050 Bruxelles
Mail: jcigar@ulb.ac.be
@biobel: http://biobel.biodiversity.be/person/show/471
Tel : 02 650 57 52