Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Data Bases > Pgsql Performance > Group by more e...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 1 of 14 Topic 4002 of 4154
Post > Topic >>

Group by more efficient than distinct?

by lists@[EMAIL PROTECTED] (Francisco Reyes) Apr 17, 2008 at 11:46 PM

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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




 14 Posts in Topic:
Group by more efficient than distinct?
lists@[EMAIL PROTECTED]   2008-04-17 23:46:08 
Re: Group by more efficient than distinct?
mlists@[EMAIL PROTECTED]   2008-04-18 09:25:04 
Re: Group by more efficient than distinct?
stark@[EMAIL PROTECTED]   2008-04-18 10:36:02 
Re: Group by more efficient than distinct?
lists@[EMAIL PROTECTED]   2008-04-18 12:35:04 
Re: Group by more efficient than distinct?
lists@[EMAIL PROTECTED]   2008-04-20 11:15:36 
Re: Group by more efficient than distinct?
lists@[EMAIL PROTECTED]   2008-04-22 01:34:40 
Re: Group by more efficient than distinct?
mark@[EMAIL PROTECTED] (  2008-04-21 19:50:22 
Re: Group by more efficient than distinct?
mark@[EMAIL PROTECTED] (  2008-04-21 21:39:15 
Re: Group by more efficient than distinct?
lists@[EMAIL PROTECTED]   2008-04-20 11:12:10 
Re: Group by more efficient than distinct?
llonergan@[EMAIL PROTECTE  2008-04-20 22:35:58 
Re: Group by more efficient than distinct?
matthew@[EMAIL PROTECTED]  2008-04-22 11:34:23 
Re: Group by more efficient than distinct?
mark@[EMAIL PROTECTED] (  2008-04-22 08:01:20 
Re: Group by more efficient than distinct?
matthew@[EMAIL PROTECTED]  2008-04-22 13:22:20 
Re: Group by more efficient than distinct?
mark@[EMAIL PROTECTED] (  2008-04-22 09:04:30 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan13V112 Fri Jul 4 22:20:42 CDT 2008.