From: Ang Chin Han <ang.chin.han@gmail.com>
To: pgsql-performance@postgresql.org
Subject: DISTINCT and GROUP BY: possible performance enhancement?
Date: Sun, 7 Nov 2004 19:04:16 +0800
Message-ID: <8c88543604110703044cf70e15@mail.gmail.com> (raw)
Is
SELECT DISTINCT foo, bar FROM baz;
equivalent to
SELECT foo, bar from baz GROUP BY foo, bar;
?
In the former case, pgsql >= 7.4 does not use HashAgg, but uses it for
the latter case. In many circumstances, esp. for large amount of data
in the table baz, the second case is an order of a magnitude faster.
For example (pgsql8b4):
regress=# explain analyze select distinct four from tenk1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Unique (cost=1109.39..1159.39 rows=4 width=4) (actual
time=90.017..106.936 rows=4 loops=1)
-> Sort (cost=1109.39..1134.39 rows=10000 width=4) (actual
time=90.008..95.589 rows=10000 loops=1)
Sort Key: four
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=4)
(actual time=0.027..45.454 rows=10000 loops=1)
Total runtime: 110.927 ms
(5 rows)
regress=# explain analyze select distinct four from tenk1 group by four;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Unique (cost=470.04..470.06 rows=4 width=4) (actual
time=47.487..47.498 rows=4 loops=1)
-> Sort (cost=470.04..470.05 rows=4 width=4) (actual
time=47.484..47.486 rows=4 loops=1)
Sort Key: four
-> HashAggregate (cost=470.00..470.00 rows=4 width=4)
(actual time=47.444..47.451 rows=4 loops=1)
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000
width=4) (actual time=0.013..31.068 rows=10000 loops=1)
Total runtime: 47.822 ms
(6 rows)
If they're equivalent, can we have pgsql use HashAgg for DISTINCTs?
Yes, I've read planner.c's comments on "Executor doesn't support
hashed aggregation with DISTINCT aggregates.", but I believe using
HashAgg is better if the product of the columns' n_distinct statistic
is way less than the number of expected rows.
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: ang.chin.han@gmail.com
Subject: Re: DISTINCT and GROUP BY: possible performance enhancement?
In-Reply-To: <8c88543604110703044cf70e15@mail.gmail.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