From: Bart Degryse <Bart.Degryse@indicator.be>
To: pgsql-sql <pgsql-sql@postgresql.org>
To: Julien <jcigar@ulb.ac.be>
Subject: Re: in() VS exists()
Date: Thu, 13 Mar 2008 14:46:14 +0100
Message-ID: <47D93E36.A3DD.0030.0@indicator.be> (raw)
In-Reply-To: <1205419644.3240.19.camel@frodon.be-bif.ulb.ac.be>
References: <1205419644.3240.19.camel@frodon.be-bif.ulb.ac.be>
The chapter on indexes in the manual (
http://www.postgresql.org/docs/8.2/static/indexes.html )should give
you a pretty good idea on the why.
IN and EXISTS are not the only possibilities, you can also use inner or
outer joins.
Which solution performs best depends on the data, the database version,
the available indexes, ...
>>> Julien <jcigar@ulb.ac.be> 2008-03-13 15:47 >>>
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 ( 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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
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: Bart.Degryse@indicator.be, jcigar@ulb.ac.be
Subject: Re: in() VS exists()
In-Reply-To: <47D93E36.A3DD.0030.0@indicator.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