X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 799555E47F2 for ; Mon, 6 Sep 2004 16:56:34 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 53567-08 for ; Mon, 6 Sep 2004 15:56:29 +0000 (GMT) Received: from mail.logi-track.com (burro.logi-track.com [213.239.193.212]) by svr1.postgresql.org (Postfix) with ESMTP id 79F5C5E4893 for ; Mon, 6 Sep 2004 16:56:27 +0100 (BST) Received: from kingfisher.intern.logi-track.com (G6e9f.g.pppool.de [80.185.110.159]) by mail.logi-track.com (Postfix) with ESMTP id 09874300E8; Mon, 6 Sep 2004 17:56:27 +0200 (CEST) Received: from kingfisher.intern.logi-track.com (localhost [127.0.0.1]) by kingfisher.intern.logi-track.com (Postfix) with SMTP id 23EEDAB2F3; Mon, 6 Sep 2004 17:56:20 +0200 (CEST) Date: Mon, 6 Sep 2004 17:56:18 +0200 From: Markus Schaber To: PostgreSQL Performance List Subject: Re: Multiple Uniques Message-ID: <20040906175618.5497a124@kingfisher.intern.logi-track.com> In-Reply-To: <87isawwjn1.fsf@stark.xeocode.com> References: <20040902154340.5b1a669a@kingfisher.intern.logi-track.com> <87isawwjn1.fsf@stark.xeocode.com> Organization: logi-track ag, =?ISO-8859-15?Q?z=FCrich?= X-Mailer: Sylpheed-Claws 0.9.12a (GTK+ 1.2.10; i386-pc-linux-gnu) X-Face: Nx5T&>Nj$VrVPv}sC3IL&)TqHHOKCz/|)R$i"*r@w0{*I6w; UNU_hdl1J4NI_m{IMztq=>cmM}1gCLbAF+9\#CGkG8}Y{x%SuQ>1#t:; Z(|\qdd[i]HStki~#w1$TPF}:0w-7"S\Ev|_a$K wrote: > Markus Schaber writes: >=20 > > logigis=3D# explain select count(id) from (select ref_in_id as id from= streets union select nref_in_id as id from streets) as blubb; > > QUERY PLAN=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 > > -----------------------------------------------------------------------= ---------------------------------- > > Aggregate (cost=3D16220893.16..16220893.16 rows=3D1 width=3D8) > > -> Subquery Scan blubb (cost=3D15254815.03..16082881.99 rows=3D552= 04464 width=3D8) > > -> Unique (cost=3D15254815.03..15530837.35 rows=3D55204464 w= idth=3D8) > > -> Sort (cost=3D15254815.03..15392826.19 rows=3D552044= 64 width=3D8) > > Sort Key: id > > -> Append (cost=3D0.00..6810225.28 rows=3D552044= 64 width=3D8) > > -> Subquery Scan "*SELECT* 1" (cost=3D0.00= ..3405112.64 rows=3D27602232 width=3D8) > > -> Seq Scan on streets (cost=3D0.00.= .3129090.32 rows=3D27602232 width=3D8) > > -> Subquery Scan "*SELECT* 2" (cost=3D0.00= ..3405112.64 rows=3D27602232 width=3D8) > > -> Seq Scan on streets (cost=3D0.00.= .3129090.32 rows=3D27602232 width=3D8) >=20 > You can actually go one step further and do: >=20 > select count(distinct id) from (select ... union all select ...) as blub= b; Hmm, as query plan, it gives me: > logigis=3D# explain select count(distinct id) from (select ref_in_id as i= d from streets union all select nref_in_id as id from streets) as blubb; > QUERY PLAN=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20 > -------------------------------------------------------------------------= -------------------- > Aggregate (cost=3D7500281.08..7500281.08 rows=3D1 width=3D8) > -> Subquery Scan blubb (cost=3D0.00..7362269.92 rows=3D55204464 widt= h=3D8) > -> Append (cost=3D0.00..6810225.28 rows=3D55204464 width=3D8) > -> Subquery Scan "*SELECT* 1" (cost=3D0.00..3405112.64 r= ows=3D27602232 width=3D8) > -> Seq Scan on streets (cost=3D0.00..3129090.32 ro= ws=3D27602232 width=3D8) > -> Subquery Scan "*SELECT* 2" (cost=3D0.00..3405112.64 r= ows=3D27602232 width=3D8) > -> Seq Scan on streets (cost=3D0.00..3129090.32 ro= ws=3D27602232 width=3D8) > (7 rows) This leaves off the sort and unique completely, and leaves all work to the count aggrecation. Maybe this is implemented by hashing and thus somehow more efficient... > I'm not sure why this is any faster since it still has to do all the same > work, but it's a different code path and it seems to be about 20% faster = for > me. When comparing the actual timings, I get: > logigis=3D# \timing > Timing is on. > logigis=3D# select count(distinct id) from (select ref_in_id as id from s= treets union all select nref_in_id as id from streets) as blubb; > count=20=20=20 > ---------- > 20225596 > (1 row) >=20 > Time: 1339098.226 ms > logigis=3D# select count(id) from (select ref_in_id as id from streets u= nion select nref_in_id as id from streets) as blubb; > count=20=20=20 > ---------- > 20225596 > (1 row) >=20 > Time: 1558920.784 ms > logigis=3D#=20 So you're right, its faster this way. There seems to be some room to play with optimizer enhancements. But simply removing all distincts from subselects would not be the best way to go, as reducing intermediate datasets can also improve the performance. Markus --=20 markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 z=FCrich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:schabios@logi-track.com | www.logi-track.com