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 206025E473C for ; Thu, 2 Sep 2004 10:43:47 -0300 (ADT) 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 61572-10 for ; Thu, 2 Sep 2004 13:43:43 +0000 (GMT) Received: from mail.logi-track.com (www.logi-track.com [213.239.193.212]) by svr1.postgresql.org (Postfix) with ESMTP id 984CC5E472F for ; Thu, 2 Sep 2004 10:43:40 -0300 (ADT) Received: from kingfisher.intern.logi-track.com (G7008.g.pppool.de [80.185.112.8]) by mail.logi-track.com (Postfix) with ESMTP id 75B1130139; Thu, 2 Sep 2004 15:43:46 +0200 (CEST) Received: from kingfisher.intern.logi-track.com (localhost [127.0.0.1]) by kingfisher.intern.logi-track.com (Postfix) with SMTP id 07286AB2A9; Thu, 2 Sep 2004 15:43:40 +0200 (CEST) Date: Thu, 2 Sep 2004 15:43:40 +0200 From: Markus Schaber To: PostgreSQL Performance List Subject: Multiple Uniques Message-Id: <20040902154340.5b1a669a@kingfisher.intern.logi-track.com> Organization: logi-track ag, =?ISO-8859-15?Q?z=FCrich?= X-Mailer: Sylpheed-Claws 0.9.12 (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 Subquery Scan blabb (cost=3D32970061.50..33246085.82 rows=3D200 wid= th=3D8) -> Unique (cost=3D32970061.50..33246083.82 rows=3D200 width=3D8) -> Sort (cost=3D32970061.50..33108072.66 rows=3D55204464 w= idth=3D8) Sort Key: id -> Subquery Scan blubb (cost=3D23697404.79..24525471= .75 rows=3D55204464 width=3D8) -> Unique (cost=3D23697404.79..23973427.11 row= s=3D55204464 width=3D8) -> Sort (cost=3D23697404.79..23835415.95= rows=3D55204464 width=3D8) Sort Key: id -> Append (cost=3D7212374.04..1525= 2815.03 rows=3D55204464 width=3D8) -> Subquery Scan "*SELECT* 1"= (cost=3D7212374.04..7626407.52 rows=3D27602232 width=3D8) -> Unique (cost=3D7212= 374.04..7350385.20 rows=3D27602232 width=3D8) -> Sort (cost=3D= 7212374.04..7281379.62 rows=3D27602232 width=3D8) Sort Key: re= f_in_id -> Seq Scan= on streets (cost=3D0.00..3129090.32 rows=3D27602232 width=3D8) -> Subquery Scan "*SELECT* 2"= (cost=3D7212374.04..7626407.52 rows=3D27602232 width=3D8) -> Unique (cost=3D7212= 374.04..7350385.20 rows=3D27602232 width=3D8) -> Sort (cost=3D= 7212374.04..7281379.62 rows=3D27602232 width=3D8) Sort Key: nr= ef_in_id -> Seq Scan= on streets (cost=3D0.00..3129090.32 rows=3D27602232 width=3D8) (20 rows) I might have to add that this query is not an every-day query, it's merely part of some statistics that a colleague needs for some estimations as he has to create a tool that works on this data. Both ref_in_id and nref_in_id are non-indexed columns with type int8. I was somehow irritated by the fact that the Query Plan contains 4 Uniques. Then, I tried the following query: logigis=3D# explain select count(id) from (select distinct id from (select = ref_in_id as id from streets union select nref_in_id as id from streets) = as blubb) as blabb; 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=20=20=20=20=20= =20=20=20=20 ---------------------------------------------------------------------------= ------------------------------------------------ Aggregate (cost=3D24803496.57..24803496.57 rows=3D1 width=3D8) -> Subquery Scan blabb (cost=3D24527471.75..24803496.07 rows=3D200 wid= th=3D8) -> Unique (cost=3D24527471.75..24803494.07 rows=3D200 width=3D8) -> Sort (cost=3D24527471.75..24665482.91 rows=3D55204464 w= idth=3D8) Sort Key: id -> Subquery Scan blubb (cost=3D15254815.03..16082881= .99 rows=3D55204464 width=3D8) -> Unique (cost=3D15254815.03..15530837.35 row= s=3D55204464 width=3D8) -> Sort (cost=3D15254815.03..15392826.19= rows=3D55204464 width=3D8) Sort Key: id -> Append (cost=3D0.00..6810225.28= rows=3D55204464 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) (14 rows) And after re-parsing the documentation about UNION, the following one: logigis=3D# explain select count(id) from (select ref_in_id as id from str= eets 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=3D5520446= 4 width=3D8) -> Unique (cost=3D15254815.03..15530837.35 rows=3D55204464 width= =3D8) -> Sort (cost=3D15254815.03..15392826.19 rows=3D55204464 w= idth=3D8) Sort Key: id -> Append (cost=3D0.00..6810225.28 rows=3D55204464 w= idth=3D8) -> Subquery Scan "*SELECT* 1" (cost=3D0.00..34= 05112.64 rows=3D27602232 width=3D8) -> Seq Scan on streets (cost=3D0.00..312= 9090.32 rows=3D27602232 width=3D8) -> Subquery Scan "*SELECT* 2" (cost=3D0.00..34= 05112.64 rows=3D27602232 width=3D8) -> Seq Scan on streets (cost=3D0.00..312= 9090.32 rows=3D27602232 width=3D8) (10 rows) Those queries should give the same result. So, now my question is, why does the query optimizer not recognize that it can throw away those "non-unique" Sort/Unique passes? Is PostGreSQL 8 capable of this optimization? Thanks, 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