Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id E8B902E2F40
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 13 Mar 2008 11:38:01 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 73044-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 13 Mar 2008 11:37:58 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from interscan.indicator.com (unknown [193.121.199.3])
	by postgresql.org (Postfix) with ESMTP id 2BF492E2F65
	for <pgsql-sql@postgresql.org>; Thu, 13 Mar 2008 11:37:57 -0300 (ADT)
Received: from DOMEXT-MTA by WEBACCESS.INDICATOR.BEwith Novell_GroupWise;
	Thu, 13 Mar 2008 14:46:22 +0100
Message-Id: <47D93E36.A3DD.0030.0@indicator.be>
X-Mailer: Novell GroupWise Internet Agent 7.0.2 
Date: Thu, 13 Mar 2008 14:46:14 +0100
From: "Bart Degryse" <Bart.Degryse@indicator.be>
To: "pgsql-sql" <pgsql-sql@postgresql.org>,
	"Julien" <jcigar@ulb.ac.be>
Subject: Re: in() VS exists()
References: <1205419644.3240.19.camel@frodon.be-bif.ulb.ac.be>
In-Reply-To: <1205419644.3240.19.camel@frodon.be-bif.ulb.ac.be>
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="=__PartF0D66336.0__="
X-imss-version: 2.050
X-imss-result: Passed
X-imss-approveListMatch: *@indicator.be
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/71
X-Sequence-Number: 30399

--=__PartF0D66336.0__=
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

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

--=__PartF0D66336.0__=
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.2800.1264" name=3DGENERATOR></HEAD>
<BODY style=3D"MARGIN: 4px 4px 1px; FONT: 10pt Tahoma">
<DIV>The <A href=3D"http://www.postgresql.org/docs/8.2/static/indexes.html"=
>chapter on indexes in the manual </A>should give you a pretty good idea =
on the why.</DIV>
<DIV>IN and EXISTS are not the only possibilities, you can also use inner =
or outer joins.</DIV>
<DIV>Which solution performs best depends on the data, the database =
version, the available indexes, ...<BR><BR>&gt;&gt;&gt; Julien &lt;jcigar@u=
lb.ac.be&gt; 2008-03-13 15:47 &gt;&gt;&gt;<BR>Hello,<BR><BR>Does anyone =
has an idea why sometimes:<BR>- select ... where ... in (select ...)<BR>is =
faster than :<BR>- select ... where ... exists(select ...)<BR>and =
sometimes it's the opposite ?<BR><BR>I had such a situation, I've pasted =
the queries on:<BR><A href=3D"http://rafb.net/p/KXNZ6892.html">http://rafb.=
net/p/KXNZ6892.html</A> and <A href=3D"http://rafb.net/p/jvo5DO38.html">htt=
p://rafb.net/p/jvo5DO38.html</A><BR><BR>It's running PostgreSQL 8.1 with =
an effective_cache_size of 30000.<BR><BR>specimens.id is the primary key =
and there are indexes on<BR>sequences(specimen_id) and specimen_measurement=
s(specimen_id)<BR><BR>Is there a general "rule" to know when to use the =
in() version and when<BR>to use the exists() version ? Is it true to say =
that the exists()<BR>version is more scalable (with many rows) than the =
in() version (from<BR>the little tests I made it seems the case) ?<BR><BR>T=
hanks,<BR>Julien<BR><BR>-- <BR>Julien Cigar<BR>Belgian Biodiversity =
Platform<BR><A href=3D"http://www.biodiversity.be/">http://www.biodiversity=
.be</A><BR>Universit=C3=A9 Libre de Bruxelles (ULB)<BR>Campus de la Plaine =
CP 257<BR>B=C3=A2timent NO, Bureau 4 N4 115C (Niveau 4)<BR>Boulevard du =
Triomphe, entr=C3=A9e ULB 2<BR>B-1050 Bruxelles<BR>Mail: jcigar@ulb.ac.be<B=
R>@biobel: <A href=3D"http://biobel.biodiversity.be/person/show/471">http:/=
/biobel.biodiversity.be/person/show/471</A><BR>Tel : 02 650 57 52<BR><BR><B=
R>-- <BR>Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)<BR>To =
make changes to your subscription:<BR><A href=3D"http://www.postgresql.org/=
mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</A><BR></D=
IV></BODY></HTML>

--=__PartF0D66336.0__=--