Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id EC00F2E0048 for ; Mon, 21 Apr 2008 02:36:08 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 80509-05 for ; Mon, 21 Apr 2008 02:36:03 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from exprod6og104.obsmtp.com (exprod6og104.obsmtp.com [64.18.1.187]) by developer.postgresql.org (Postfix) with SMTP id 4CE9C2E0031 for ; Mon, 21 Apr 2008 02:36:03 -0300 (ADT) Received: from source ([63.240.6.3]) (using TLSv1) by exprod6ob104.postini.com ([64.18.5.12]) with SMTP; Sun, 20 Apr 2008 22:36:01 PDT Received: from D01HOST03.Mi8.com ([172.16.1.25]) by Outbound01.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 21 Apr 2008 01:38:31 -0400 Received: from MI8NYCMAIL08.Mi8.com ([172.16.1.165]) by D01HOST03.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 21 Apr 2008 01:36:01 -0400 Received: from 69.181.101.67 ([69.181.101.67]) by MI8NYCMAIL08.Mi8.com ([172.16.1.190]) via Exchange Front-End Server mi8owa.mi8.com ([172.16.1.73]) with Microsoft Exchange Server HTTP-DAV ; Mon, 21 Apr 2008 05:38:30 +0000 User-Agent: Microsoft-Entourage/12.1.0.080305 Date: Sun, 20 Apr 2008 22:35:58 -0700 Subject: Re: Group by more efficient than distinct? From: Luke Lonergan To: Francisco Reyes , Pgsql performance Message-ID: Thread-Topic: [PERFORM] Group by more efficient than distinct? Thread-Index: AcihCGwu8LHPtNBPRX+wrV9aZwzrFgCaSc9I In-Reply-To: Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3291575759_709166" X-OriginalArrivalTime: 21 Apr 2008 05:36:01.0050 (UTC) FILETIME=[953D17A0:01C8A371] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.021 tagged_above=0 required=5 tests=AWL=-0.398, HTML_MESSAGE=0.001, MIME_QP_LONG_LINE=1.819, RCVD_IN_DNSWL_MED=-4, RCVD_NUMERIC_HELO=2.599 X-Spam-Level: X-Archive-Number: 200804/296 X-Sequence-Number: 29789 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3291575759_709166 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Hi Francisco, Generally, PG sorting is much slower than hash aggregation for performing the distinct operation. There may be small sizes where this isn=B9t true, bu= t for large amounts of data (in-memory or not), hash agg (used most often, bu= t not always by GROUP BY) is faster. We=B9ve implemented a special optimization to PG sorting that does the distinct processing within the sort, instead of afterward, but it=B9s limited to some small-ish number (10,000) of distinct values due to it=B9s use of a memory and processing intensive heap. So, you=B9re better off using GROUP BY and making sure that the planner is using hash agg to do the work. - Luke=20 On 4/17/08 8:46 PM, "Francisco Reyes" wrote: > I am trying to get a distinct set of rows from 2 tables. > After looking at someone else's query I noticed they were doing a group b= y > to obtain the unique list. >=20 > After comparing on multiple machines with several tables, it seems using > group by to obtain a distinct list is substantially faster than using > select distinct. >=20 > Is there any dissadvantage of using "group by" to obtain a unique list? >=20 > On a small dataset the difference was about 20% percent. >=20 > Group by > HashAggregate (cost=3D369.61..381.12 rows=3D1151 width=3D8) (actual > time=3D76.641..85.167 rows=3D2890 loops=3D1) >=20 > Distinct > Unique (cost=3D1088.23..1174.53 rows=3D1151 width=3D8) (actual > time=3D90.516..140.123 rows=3D2890 loops=3D1) >=20 > Although I don't have the numbers here with me, a simmilar result was > obtaining against a query that would return 100,000 rows. 20% and more > speed differnce between "group by" over "select distinct". >=20 > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org= ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >=20 --B_3291575759_709166 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable Re: [PERFORM] Group by more efficient than distinct? Hi Francisco,

Generally, PG sorting is much slower than hash aggregation for performing t= he distinct operation.  There may be small sizes where this isn’t= true, but for large amounts of data (in-memory or not), hash agg (used most= often, but not always by GROUP BY) is faster.

We’ve implemented a special optimization to PG sorting that does the = distinct processing within the sort, instead of afterward, but it’s li= mited to some small-ish number (10,000) of distinct values due to it’s= use of a memory and processing intensive heap.

So, you’re better off using GROUP BY and making sure that the planner= is using hash agg to do the work.

- Luke


On 4/17/08 8:46 PM, "Francisco Reyes" <lists@stringsutils.com&= gt; wrote:

I am trying to get a distinct set o= f rows from 2 tables.
After looking at someone else's query I noticed they were doing a group by<= BR> to obtain the unique list.

After comparing on multiple machines with several tables, it seems using group by to obtain a distinct list is substantially faster than using
select distinct.

Is there any dissadvantage of using "group by" to obtain a unique= list?

On a small dataset the difference was about 20% percent.

Group by
 HashAggregate  (cost=3D369.61..381.12 rows=3D1151 width=3D8) (actual time=3D76.641..85.167 rows=3D2890 loops=3D1)

Distinct
 Unique  (cost=3D1088.23..1174.53 rows=3D1151 width=3D8) (actual
time=3D90.516..140.123 rows=3D2890 loops=3D1)

Although I don't have the numbers here with me, a simmilar result was
obtaining against a query that would return 100,000 rows. 20% and more
speed differnce between "group by" over "select distinct&quo= t;.  

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)<= BR> To make changes to your subscription:
http://www.p= ostgresql.org/mailpref/pgsql-performance

--B_3291575759_709166--