Which solution performs best depends on the data, the database = version, the available indexes, ...

>>> Julien <jcigar@u= lb.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 htt= p://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_measurement= s(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) ?

T= hanks,
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
--=__PartF0D66336.0__=-- From Bart.Degryse@indicator.be Thu Mar 13 14:13:13 2008 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__=-- From Bart.Degryse@indicator.be Thu Mar 13 14:28:18 2008 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 4C2802E003F for ; Thu, 13 Mar 2008 11:28:18 -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 59868-03 for ; Thu, 13 Mar 2008 11:28:05 -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 8E2E02E0135 for ; Thu, 13 Mar 2008 11:28:13 -0300 (ADT) Received: from DOMEXT-MTA by WEBACCESS.INDICATOR.BEwith Novell_GroupWise; Thu, 13 Mar 2008 15:28:10 +0100 Message-Id: <47D94804.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Thu, 13 Mar 2008 15:28:04 +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><47 D94471.A3DD.0030.0@indicator.be><1205425663.3240.35.camel@frodon.be-bif.ulb.ac.be> In-Reply-To: <1205425663.3240.35.camel@frodon.be-bif.ulb.ac.be> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__Part2B0DB8E4.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/70 X-Sequence-Number: 30398 --=__Part2B0DB8E4.0__= Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit how about select count(distinct s.specimen_id) from specimens sp INNER JOIN sequences s on s.specimen_id = sp.id; >>> Julien 2008-03-13 17:27 >>> mmh no because it's a one to many relation (a specimen can have more than one sequence) : muridae=> select count(sp.id) from specimens sp INNER JOIN sequences s on s.specimen_id = sp.id; count ------- 1536 (1 row) Time: 81.242 ms muridae=> select count(sp.id) from specimens sp where sp.id in (select specimen_id from sequences group by specimen_id); count ------- 1431 (1 row) Time: 81.736 ms muridae=> (of course this is a bad example, because I could just do: select count(specimen_id) from sequences group by specimen_id;, but in my application I have more fields coming from specimens of course) Julien On Thu, 2008-03-13 at 15:12 +0100, Bart Degryse wrote: > 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 > -- 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 --=__Part2B0DB8E4.0__= Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

how about

select count(distinct s.specimen_id) from specimens sp INNER = JOIN sequences s

on s.specimen_id =3D sp.id;

>>> Julien <jcigar@= ulb.ac.be> 2008-03-13 17:27 >>>
mmh no because it's a one = to many relation (a specimen can have more
than one sequence) :

m= uridae=3D> select count(sp.id) from specimens sp INNER JOIN sequences = s
on s.specimen_id =3D sp.id;
count
-------
1536
(1 = row)

Time: 81.242 ms
muridae=3D> select count(sp.id) from = specimens sp where sp.id in (select
specimen_id from sequences group by = specimen_id);
count
-------
1431
(1 row)

Time: = 81.736 ms
muridae=3D>

(of course this is a bad example, = because I could just do: select
count(specimen_id) from sequences group = by specimen_id;, but in my
application I have more fields coming from = specimens of course)

Julien

On Thu, 2008-03-13 at 15:12 = +0100, Bart Degryse wrote:
> 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)
>
> 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 =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 >>>
> = > 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/jvo5DO3= 8.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/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

--=__Part2B0DB8E4.0__=-- From jcigar@ulb.ac.be Thu Mar 13 12:52:53 2008 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 775792E0293 for ; Thu, 13 Mar 2008 09:52:53 -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 27903-01-9 for ; Thu, 13 Mar 2008 09:52:40 -0300 (ADT) X-Greylist: delayed 00:11:06.620103 by SQLgrey-1.7.5 Received: from mxout.ulb.ac.be (mxout.ulb.ac.be [164.15.128.111]) by postgresql.org (Postfix) with ESMTP id C05992E2D53 for ; Thu, 13 Mar 2008 09:52:24 -0300 (ADT) X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AqABAH692EekD30E/2dsb2JhbAAIqBg Received: from bebif01.ulb.ac.be (HELO [10.0.0.194]) ([164.15.125.4]) by mxout.ulb.ac.be with ESMTP; 13 Mar 2008 13:41:09 +0100 Subject: in() VS exists() From: Julien To: pgsql-sql 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 X-Mailer: Evolution 2.12.3 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL=0.001 X-Spam-Level: X-Archive-Number: 200803/66 X-Sequence-Number: 30394 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 From Bart.Degryse@indicator.be Thu Mar 13 14:50:23 2008 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 86C962E0294 for ; Thu, 13 Mar 2008 11:50:23 -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 65720-04 for ; Thu, 13 Mar 2008 11:50:08 -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 4ECCD2E020C for ; Thu, 13 Mar 2008 11:50:17 -0300 (ADT) Received: from DOMEXT-MTA by WEBACCESS.INDICATOR.BEwith Novell_GroupWise; Thu, 13 Mar 2008 15:50:14 +0100 Message-Id: <47D94D2E.A3DD.0030.0@indicator.be> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Thu, 13 Mar 2008 15:50:06 +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> <47D94471.A3DD.0030.0@indicator.be><1205425663.3240.35.camel@frodon.be-bif. ulb.ac.be><47D94804.A3DD.0030.0@indicator.be><1205427041.3240.44.camel@frodon.be-bif.ulb.ac.be> In-Reply-To: <1205427041.3240.44.camel@frodon.be-bif.ulb.ac.be> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="=__PartC7E1540E.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/73 X-Sequence-Number: 30401 --=__PartC7E1540E.0__= Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit So it all comes back to what I wrote in the beginning: "Which solution performs best depends on the data, the database version, the available indexes, ..." Tips: - be aware that statements can be written in different ways - test them on realistic data - use explain to tune your statements >>> Julien 2008-03-13 17:50 >>> >From my experience I tend to avoid SELECT DISTINCT queries because it's usually very slow with many rows ... For my specific case the result is the same: muridae=> select count(distinct s.specimen_id) from specimens sp INNER JOIN sequences s on s.specimen_id = sp.id; count ------- 1431 (1 row) Time: 65.351 ms muridae=> select count(sp.id) from specimens sp where sp.id in (select specimen_id from sequences group by specimen_id); count ------- 1431 (1 row) Time: 66.371 ms But to give an example, I have a table with ~1 000 000 rows where the DISTINCT solution is more than 10 times slower : muridae=> select count(distinct sp.id) from specimens sp INNER JOIN specimen_measurements m ON m.specimen_id = sp.id; count ------- 75241 (1 row) Time: 15970.668 ms muridae=> select count(sp.id) from specimens sp INNER JOIN (select specimen_id from specimen_measurements GROUP BY specimen_id) as foo on foo.specimen_id = sp.id; count ------- 75241 (1 row) Time: 1165.487 ms Regards, Julien On Thu, 2008-03-13 at 15:28 +0100, Bart Degryse wrote: > how about > select count(distinct s.specimen_id) from specimens sp INNER JOIN > sequences s > on s.specimen_id = sp.id; > > > >>> Julien 2008-03-13 17:27 >>> > mmh no because it's a one to many relation (a specimen can have more > than one sequence) : > > muridae=> select count(sp.id) from specimens sp INNER JOIN sequences s > on s.specimen_id = sp.id; > count > ------- > 1536 > (1 row) > > Time: 81.242 ms > muridae=> select count(sp.id) from specimens sp where sp.id in (select > specimen_id from sequences group by specimen_id); > count > ------- > 1431 > (1 row) > > Time: 81.736 ms > muridae=> > > (of course this is a bad example, because I could just do: select > count(specimen_id) from sequences group by specimen_id;, but in my > application I have more fields coming from specimens of course) > > Julien > > On Thu, 2008-03-13 at 15:12 +0100, Bart Degryse wrote: > > 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 > > > -- > 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 --=__PartC7E1540E.0__= Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

So it all comes back to what I wrote in the beginning:

"Which solution performs best depends on the data, the database v= ersion, the available indexes, ..."

Tips:

- be aware that statements can be written in different = ways

- test them on realistic data

- use explain to tune your statements

>>> = Julien <jcigar@ulb.ac.be> 2008-03-13 17:50 >>>
>From = my experience I tend to avoid SELECT DISTINCT queries because it's
usual= ly very slow with many rows ...

For my specific case the result is = the same:

muridae=3D> select count(distinct s.specimen_id) from = specimens sp INNER
JOIN sequences s on s.specimen_id =3D sp.id;
count=
-------
1431
(1 row)

Time: 65.351 ms
muridae=3D= > select count(sp.id) from specimens sp where sp.id in (select
specim= en_id from sequences group by specimen_id);
count
-------
= 1431
(1 row)

Time: 66.371 ms

But to give an example, I = have a table with ~1 000 000 rows where the
DISTINCT solution is more = than 10 times slower :

muridae=3D> select count(distinct sp.id) = from specimens sp INNER JOIN
specimen_measurements m ON m.specimen_id = =3D sp.id;
count
-------
75241
(1 row)

Time: 15970.668 = ms

muridae=3D> select count(sp.id) from specimens sp INNER JOIN = (select
specimen_id from specimen_measurements GROUP BY specimen_id) as = foo on
foo.specimen_id =3D sp.id;
count
-------
75241
(1 = row)

Time: 1165.487 ms

Regards,
Julien

On Thu, = 2008-03-13 at 15:28 +0100, Bart Degryse wrote:
> how about
> = select count(distinct s.specimen_id) from specimens sp INNER JOIN
> = sequences s
> on s.specimen_id =3D sp.id;
>
>
> = >>> Julien <jcigar@ulb.ac.be> 2008-03-13 17:27 >>><= BR>> mmh no because it's a one to many relation (a specimen can have = more
> than one sequence) :
>
> muridae=3D> select = count(sp.id) from specimens sp INNER JOIN sequences s
> on s.specimen= _id =3D sp.id;
> count
> -------
>   = 1536
> (1 row)
>
> Time: 81.242 ms
> muridae=3D>= ; select count(sp.id) from specimens sp where sp.id in (select
> = specimen_id from sequences group by specimen_id);
> count
> = -------
>   1431
> (1 row)
>
> Time: = 81.736 ms
> muridae=3D>
>
> (of course this is a = bad example, because I could just do: select
> count(specimen_id) = from sequences group by specimen_id;, but in my
> application I have = more fields coming from specimens of course)
>
> Julien
>= ;
> On Thu, 2008-03-13 at 15:12 +0100, Bart Degryse wrote:
> = > 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)
> >      &nb= sp;   Hash Cond: ("outer".id =3D "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 =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 = >>>
> > > 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(speci= men_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()
&= gt; > > 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.b= e
> > > 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
&= gt; > >
> > --
> > 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/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

--=__PartC7E1540E.0__=-- From jcigar@ulb.ac.be Thu Mar 13 14:04:38 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id B059D2E2FEE for ; Thu, 13 Mar 2008 11:04:38 -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 61475-03-4 for ; Thu, 13 Mar 2008 11:04:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mxout.ulb.ac.be (mxout.ulb.ac.be [164.15.128.111]) by postgresql.org (Postfix) with ESMTP id 029692E2DD5 for ; Thu, 13 Mar 2008 11:04:18 -0300 (ADT) X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AqABAFTR2EekD30E/2dsb2JhbAAIqFM Received: from bebif01.ulb.ac.be (HELO [10.0.0.194]) ([164.15.125.4]) by mxout.ulb.ac.be with ESMTP; 13 Mar 2008 15:03:45 +0100 Subject: Re: in() VS exists() From: Julien To: Bart Degryse Cc: pgsql-sql In-Reply-To: <47D93E36.A3DD.0030.0@indicator.be> References: <1205419644.3240.19.camel@frodon.be-bif.ulb.ac.be> <47D93E36.A3DD.0030.0@indicator.be> Content-Type: text/plain; charset=utf-8 Date: Thu, 13 Mar 2008 17:10:00 +0100 Message-Id: <1205424600.3240.30.camel@frodon.be-bif.ulb.ac.be> Mime-Version: 1.0 X-Mailer: Evolution 2.12.3 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=AWL=0.000 X-Spam-Level: X-Archive-Number: 200803/67 X-Sequence-Number: 30395 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 > 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 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 From jcigar@ulb.ac.be Thu Mar 13 14:21:57 2008 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B0BE62E2DC2 for ; Thu, 13 Mar 2008 11:21:57 -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 56298-07 for ; Thu, 13 Mar 2008 11:21:29 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mxout.ulb.ac.be (mxout.ulb.ac.be [164.15.128.111]) by postgresql.org (Postfix) with ESMTP id A4B432E2DDA for ; Thu, 13 Mar 2008 11:21:36 -0300 (ADT) X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AqABAO/U2EekD30E/2dsb2JhbAAIqGM Received: from bebif01.ulb.ac.be (HELO [10.0.0.194]) ([164.15.125.4]) by mxout.ulb.ac.be with ESMTP; 13 Mar 2008 15:21:28 +0100 Subject: Re: in() VS exists() From: Julien To: Bart Degryse Cc: pgsql-sql In-Reply-To: <47D94471.A3DD.0030.0@indicator.be> 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> <47D94471.A3DD.0030.0@indicator.be> Content-Type: text/plain; charset=utf-8 Date: Thu, 13 Mar 2008 17:27:43 +0100 Message-Id: <1205425663.3240.35.camel@frodon.be-bif.ulb.ac.be> Mime-Version: 1.0 X-Mailer: Evolution 2.12.3 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=AWL=0.000 X-Spam-Level: X-Archive-Number: 200803/69 X-Sequence-Number: 30397 mmh no because it's a one to many relation (a specimen can have more than one sequence) : muridae=> select count(sp.id) from specimens sp INNER JOIN sequences s on s.specimen_id = sp.id; count ------- 1536 (1 row) Time: 81.242 ms muridae=> select count(sp.id) from specimens sp where sp.id in (select specimen_id from sequences group by specimen_id); count ------- 1431 (1 row) Time: 81.736 ms muridae=> (of course this is a bad example, because I could just do: select count(specimen_id) from sequences group by specimen_id;, but in my application I have more fields coming from specimens of course) Julien On Thu, 2008-03-13 at 15:12 +0100, Bart Degryse wrote: > 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 > > 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 > 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 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 From jcigar@ulb.ac.be Thu Mar 13 14:44:34 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 439FD2E020C for ; Thu, 13 Mar 2008 11:44:34 -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 74155-05 for ; Thu, 13 Mar 2008 11:44:29 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mxout.ulb.ac.be (mxout.ulb.ac.be [164.15.128.111]) by postgresql.org (Postfix) with ESMTP id 8280C2E00EA for ; Thu, 13 Mar 2008 11:44:29 -0300 (ADT) X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AoABALza2EekD30E/2dsb2JhbAAIqGM Received: from bebif01.ulb.ac.be (HELO [10.0.0.194]) ([164.15.125.4]) by mxout.ulb.ac.be with ESMTP; 13 Mar 2008 15:44:26 +0100 Subject: Re: in() VS exists() From: Julien To: Bart Degryse Cc: pgsql-sql In-Reply-To: <47D94804.A3DD.0030.0@indicator.be> 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> <47 D94471.A3DD.0030.0@indicator.be> <1205425663.3240.35.camel@frodon.be-bif.ulb.ac.be> <47D94804.A3DD.0030.0@indicator.be> Content-Type: text/plain; charset=utf-8 Date: Thu, 13 Mar 2008 17:50:41 +0100 Message-Id: <1205427041.3240.44.camel@frodon.be-bif.ulb.ac.be> Mime-Version: 1.0 X-Mailer: Evolution 2.12.3 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=AWL=0.000 X-Spam-Level: X-Archive-Number: 200803/72 X-Sequence-Number: 30400 >From my experience I tend to avoid SELECT DISTINCT queries because it's usually very slow with many rows ... For my specific case the result is the same: muridae=> select count(distinct s.specimen_id) from specimens sp INNER JOIN sequences s on s.specimen_id = sp.id; count ------- 1431 (1 row) Time: 65.351 ms muridae=> select count(sp.id) from specimens sp where sp.id in (select specimen_id from sequences group by specimen_id); count ------- 1431 (1 row) Time: 66.371 ms But to give an example, I have a table with ~1 000 000 rows where the DISTINCT solution is more than 10 times slower : muridae=> select count(distinct sp.id) from specimens sp INNER JOIN specimen_measurements m ON m.specimen_id = sp.id; count ------- 75241 (1 row) Time: 15970.668 ms muridae=> select count(sp.id) from specimens sp INNER JOIN (select specimen_id from specimen_measurements GROUP BY specimen_id) as foo on foo.specimen_id = sp.id; count ------- 75241 (1 row) Time: 1165.487 ms Regards, Julien On Thu, 2008-03-13 at 15:28 +0100, Bart Degryse wrote: > how about > select count(distinct s.specimen_id) from specimens sp INNER JOIN > sequences s > on s.specimen_id = sp.id; > > > >>> Julien 2008-03-13 17:27 >>> > mmh no because it's a one to many relation (a specimen can have more > than one sequence) : > > muridae=> select count(sp.id) from specimens sp INNER JOIN sequences s > on s.specimen_id = sp.id; > count > ------- > 1536 > (1 row) > > Time: 81.242 ms > muridae=> select count(sp.id) from specimens sp where sp.id in (select > specimen_id from sequences group by specimen_id); > count > ------- > 1431 > (1 row) > > Time: 81.736 ms > muridae=> > > (of course this is a bad example, because I could just do: select > count(specimen_id) from sequences group by specimen_id;, but in my > application I have more fields coming from specimens of course) > > Julien > > On Thu, 2008-03-13 at 15:12 +0100, Bart Degryse wrote: > > 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 > > > 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 > > 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 > 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 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