From: Gregory Stark <stark@enterprisedb.com>
To: Francisco Reyes <lists@stringsutils.com>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: Group by more efficient than distinct?
Date: Fri, 18 Apr 2008 10:36:02 +0100
Message-ID: <87ve2f7bel.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <cone.1208490368.136870.53107.1000@zoraida.natserv.net>
References: <cone.1208490368.136870.53107.1000@zoraida.natserv.net>
"Francisco Reyes" <lists@stringsutils.com> writes:
> 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)
HashAggregate needs to store more values in memory at the same time so it's
not a good plan if you have a lot of distinct values.
But the planner knows that and so as long as your work_mem is set to a
reasonable size (keeping in mind each sort or other operation feels free to
use that much itself even if there are several in the query) and the rows
estimate is reasonable accurate -- here it's mediocre but not dangerously bad
-- then if the planner is picking it it's probably a good idea.
I'm not sure but I think there are cases where the DISTINCT method wins too.
This is basically a bug, in an ideal world both queries would generate
precisely the same plans since they're equivalent. It's just not a high
priority since we have so many more interesting improvements competing for
time.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stark@enterprisedb.com, lists@stringsutils.com
Subject: Re: Group by more efficient than distinct?
In-Reply-To: <87ve2f7bel.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox