Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E87322E2D7E for ; Thu, 13 Mar 2008 11:13:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 55794-01 for ; Thu, 13 Mar 2008 11:12:57 -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 490472E02E5 for ; Thu, 13 Mar 2008 11:13:04 -0300 (ADT) Received: from DOMEXT-MTA by WEBACCESS.INDICATOR.BEwith Novell_GroupWise; Thu, 13 Mar 2008 15:13:01 +0100 Message-Id: <47D94471.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Thu, 13 Mar 2008 15:12:49 +0100 From: "Bart Degryse" To: "Julien" Cc: "pgsql-sql" Subject: Re: in() VS exists() References: <1205419644.3240.19.camel@frodon.be-bif.ulb.ac.be><47D93E36.A3DD .0030.0@indicator.be><1205424600.3240.30.camel@frodon.be-bif.ulb.ac.be> In-Reply-To: <1205424600.3240.30.camel@frodon.be-bif.ulb.ac.be> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__PartB1972271.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/68 X-Sequence-Number: 30396 --=__PartB1972271.0__= Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit I think that just select count(sp.id) from specimens sp INNER JOIN sequences s on s.specimen_id = sp.id; should be enough >>> Julien 2008-03-13 17:10 >>> If I understood well the query plan, the planner optimize the IN(SELECT ...) version with a JOIN (line 19-20 of the first paste) : -> Hash IN Join (cost=240.95..4011.20 rows=1436 width=4) (actual time=93.971..201.908 rows=1431 loops=1) Hash Cond: ("outer".id = "inner".specimen_id) so I guess that : select count(sp.id) from specimens sp where sp.id in (select specimen_id from sequences); is almost the same as : select count(sp.id) from specimens sp INNER JOIN (select specimen_id from sequences GROUP BY specimen_id) as foo on foo.specimen_id = sp.id; ? Thanks, Julien On Thu, 2008-03-13 at 14:46 +0100, Bart Degryse wrote: > The chapter on indexes in the manual 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 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 > -- 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 --=__PartB1972271.0__= Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I think that just
select count(sp.id) from specimens sp INNER JOIN sequences s on = s.specimen_id =3D sp.id;
should be enough

>>> Julien = <jcigar@ulb.ac.be> 2008-03-13 17:10 >>>
If I understood = well the query plan, the planner optimize the
IN(SELECT ...) version = with a JOIN (line 19-20 of the first paste) :

->  Hash IN = Join  (cost=3D240.95..4011.20 rows=3D1436 width=3D4) (actual
time= =3D93.971..201.908 rows=3D1431 loops=3D1)
     =     Hash Cond: ("outer".id =3D "inner".specimen_id)

s= o I guess that :

select count(sp.id) from specimens sp where sp.id = in (select specimen_id
from sequences);

is almost the same as = :

select count(sp.id) from specimens sp INNER JOIN (select = specimen_id
from sequences GROUP BY specimen_id) as foo on foo.specimen_= id =3D sp.id;

?

Thanks,
Julien

On Thu, 2008-03-13 = at 14:46 +0100, Bart Degryse wrote:
> The chapter on indexes in the = manual 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 >>><= BR>> Hello,
>
> Does anyone has an idea why sometimes:
&= gt; - 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=C3=A9 = Libre de Bruxelles (ULB)
> Campus de la Plaine CP 257
> = B=C3=A2timent NO, Bureau 4 N4 115C (Niveau 4)
> Boulevard du = Triomphe, entr=C3=A9e 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
> =
--
Julien Cigar
Belgian Biodiversity Platform
http://www.biodiversity.be
Universit=C3=A9 = Libre de Bruxelles (ULB)
Campus de la Plaine CP 257
B=C3=A2timent = NO, Bureau 4 N4 115C (Niveau 4)
Boulevard du Triomphe, entr=C3=A9e ULB = 2
B-1050 Bruxelles
Mail: jcigar@ulb.ac.be
@biobel: http://biobel.biodiversity.be/pe= rson/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
--=__PartB1972271.0__=--