Skip site navigation (1) Skip section navigation (2)

Group by more efficient than distinct?

From: Francisco Reyes <lists(at)stringsutils(dot)com>
To: Pgsql performance <pgsql-performance(at)postgresql(dot)org>
Subject: Group by more efficient than distinct?
Date: 2008-04-18 03:46:08
Message-ID: cone.1208490368.136870.53107.1000@zoraida.natserv.net (view raw or flat)
Thread:
Lists: pgsql-performance
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 by 
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=369.61..381.12 rows=1151 width=8) (actual 
time=76.641..85.167 rows=2890 loops=1)

Distinct
 Unique  (cost=1088.23..1174.53 rows=1151 width=8) (actual 
time=90.516..140.123 rows=2890 loops=1)

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".   

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2008-04-18 04:01:52
Subject: Re: SQL Function Slowness, 8.3.0
Previous:From: Craig RingerDate: 2008-04-18 03:41:17
Subject: Re: SQL Function Slowness, 8.3.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group