From: Julien <jcigar@ulb.ac.be>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: in() VS exists()
Date: Thu, 13 Mar 2008 15:47:24 +0100
Message-ID: <1205419644.3240.19.camel@frodon.be-bif.ulb.ac.be> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jcigar@ulb.ac.be
Subject: Re: in() VS exists()
In-Reply-To: <1205419644.3240.19.camel@frodon.be-bif.ulb.ac.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox