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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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".
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2008-04-18 04:01:52 | Re: SQL Function Slowness, 8.3.0 |
Previous Message | Craig Ringer | 2008-04-18 03:41:17 | Re: SQL Function Slowness, 8.3.0 |