pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDISTINCT vs. GROUP BY
4+ messages / 3 participants
[nested] [flat]
* DISTINCT vs. GROUP BY
@ 2010-02-09 21:46 Dimi Paun <dimi@lattica.com>
0 siblings, 2 replies; 4+ messages in thread
From: Dimi Paun @ 2010-02-09 21:46 UTC (permalink / raw)
To: pgsql-performance
From what I've read on the net, these should be very similar,
and should generate equivalent plans, in such cases:
SELECT DISTINCT x FROM mytable
SELECT x FROM mytable GROUP BY x
However, in my case (postgresql-server-8.1.18-2.el5_4.1),
they generated different results with quite different
execution times (73ms vs 40ms for DISTINCT and GROUP BY
respectively):
tts_server_db=# EXPLAIN ANALYZE select userdata from tagrecord where clientRmaInId = 'CPC-RMA-00110' group by userdata;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=775.68..775.69 rows=1 width=146) (actual time=40.058..40.058 rows=0 loops=1)
-> Bitmap Heap Scan on tagrecord (cost=4.00..774.96 rows=286 width=146) (actual time=40.055..40.055 rows=0 loops=1)
Recheck Cond: ((clientrmainid)::text = 'CPC-RMA-00110'::text)
-> Bitmap Index Scan on idx_tagdata_clientrmainid (cost=0.00..4.00 rows=286 width=0) (actual time=40.050..40.050 rows=0 loops=1)
Index Cond: ((clientrmainid)::text = 'CPC-RMA-00110'::text)
Total runtime: 40.121 ms
tts_server_db=# EXPLAIN ANALYZE select distinct userdata from tagrecord where clientRmaInId = 'CPC-RMA-00109';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=786.63..788.06 rows=1 width=146) (actual time=73.018..73.018 rows=0 loops=1)
-> Sort (cost=786.63..787.34 rows=286 width=146) (actual time=73.016..73.016 rows=0 loops=1)
Sort Key: userdata
-> Bitmap Heap Scan on tagrecord (cost=4.00..774.96 rows=286 width=146) (actual time=72.940..72.940 rows=0 loops=1)
Recheck Cond: ((clientrmainid)::text = 'CPC-RMA-00109'::text)
-> Bitmap Index Scan on idx_tagdata_clientrmainid (cost=0.00..4.00 rows=286 width=0) (actual time=72.936..72.936 rows=0 loops=1)
Index Cond: ((clientrmainid)::text = 'CPC-RMA-00109'::text)
Total runtime: 73.144 ms
What gives?
--
Dimi Paun <dimi@lattica.com>
Lattica, Inc.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DISTINCT vs. GROUP BY
@ 2010-02-09 22:22 Thom Brown <thombrown@gmail.com>
parent: Dimi Paun <dimi@lattica.com>
1 sibling, 0 replies; 4+ messages in thread
From: Thom Brown @ 2010-02-09 22:22 UTC (permalink / raw)
To: Dimi Paun <dimi@lattica.com>; +Cc: pgsql-performance
On 9 February 2010 21:46, Dimi Paun <dimi@lattica.com> wrote:
> >From what I've read on the net, these should be very similar,
> and should generate equivalent plans, in such cases:
>
> SELECT DISTINCT x FROM mytable
> SELECT x FROM mytable GROUP BY x
>
> However, in my case (postgresql-server-8.1.18-2.el5_4.1),
> they generated different results with quite different
> execution times (73ms vs 40ms for DISTINCT and GROUP BY
> respectively):
>
> tts_server_db=# EXPLAIN ANALYZE select userdata from tagrecord where clientRmaInId = 'CPC-RMA-00110' group by userdata;
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------
> HashAggregate (cost=775.68..775.69 rows=1 width=146) (actual time=40.058..40.058 rows=0 loops=1)
> -> Bitmap Heap Scan on tagrecord (cost=4.00..774.96 rows=286 width=146) (actual time=40.055..40.055 rows=0 loops=1)
> Recheck Cond: ((clientrmainid)::text = 'CPC-RMA-00110'::text)
> -> Bitmap Index Scan on idx_tagdata_clientrmainid (cost=0.00..4.00 rows=286 width=0) (actual time=40.050..40.050 rows=0 loops=1)
> Index Cond: ((clientrmainid)::text = 'CPC-RMA-00110'::text)
> Total runtime: 40.121 ms
>
> tts_server_db=# EXPLAIN ANALYZE select distinct userdata from tagrecord where clientRmaInId = 'CPC-RMA-00109';
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------------------------------------------
> Unique (cost=786.63..788.06 rows=1 width=146) (actual time=73.018..73.018 rows=0 loops=1)
> -> Sort (cost=786.63..787.34 rows=286 width=146) (actual time=73.016..73.016 rows=0 loops=1)
> Sort Key: userdata
> -> Bitmap Heap Scan on tagrecord (cost=4.00..774.96 rows=286 width=146) (actual time=72.940..72.940 rows=0 loops=1)
> Recheck Cond: ((clientrmainid)::text = 'CPC-RMA-00109'::text)
> -> Bitmap Index Scan on idx_tagdata_clientrmainid (cost=0.00..4.00 rows=286 width=0) (actual time=72.936..72.936 rows=0 loops=1)
> Index Cond: ((clientrmainid)::text = 'CPC-RMA-00109'::text)
> Total runtime: 73.144 ms
>
> What gives?
>
Firstly, the 2 queries aren't equal. They're matching against
different clientrmainid values.
Also, look at the bitmap index scan for each:
Bitmap Index Scan on idx_tagdata_clientrmainid (cost=0.00..4.00
rows=286 width=0) (actual time=40.050..40.050 rows=0 loops=1)
Bitmap Index Scan on idx_tagdata_clientrmainid (cost=0.00..4.00
rows=286 width=0) (actual time=72.936..72.936 rows=0 loops=1)
That's where the difference is. An identical scan takes longer in one
than the other, either due to the index scan looking for different
values in each case, or at the time you were running it, another
process was using more resources. You'd have to run these several
times to get an idea of average times.
Have you run ANALYZE on the table beforehand to make sure your stats
are up to date?
Regards
Thom
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DISTINCT vs. GROUP BY
@ 2010-02-09 22:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dimi Paun <dimi@lattica.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2010-02-09 22:38 UTC (permalink / raw)
To: Dimi Paun <dimi@lattica.com>; +Cc: pgsql-performance
Dimi Paun <dimi@lattica.com> writes:
>> From what I've read on the net, these should be very similar,
> and should generate equivalent plans, in such cases:
> SELECT DISTINCT x FROM mytable
> SELECT x FROM mytable GROUP BY x
> However, in my case (postgresql-server-8.1.18-2.el5_4.1),
> they generated different results with quite different
> execution times (73ms vs 40ms for DISTINCT and GROUP BY
> respectively):
The results certainly ought to be the same (although perhaps not with
the same ordering) --- if they aren't, please provide a reproducible
test case.
As for efficiency, though, 8.1 didn't understand how to use hash
aggregation for DISTINCT. Less-obsolete versions do know how to do
that.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DISTINCT vs. GROUP BY
@ 2010-02-10 01:43 Dimi Paun <dimi@lattica.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Dimi Paun @ 2010-02-10 01:43 UTC (permalink / raw)
To: pgsql-performance; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Mihai Popa <mihai@lattica.com>
On Tue, 2010-02-09 at 17:38 -0500, Tom Lane wrote:
> The results certainly ought to be the same (although perhaps not with
> the same ordering) --- if they aren't, please provide a reproducible
> test case.
The results are the same, this is not a problem.
> As for efficiency, though, 8.1 didn't understand how to use hash
> aggregation for DISTINCT. Less-obsolete versions do know how to do
> that.
Indeed, this seem to be the issue:
tts_server_db=# EXPLAIN ANALYZE select userdata from tagrecord where clientRmaInId = 'CPC-RMA-00110' group by userdata;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=253.34..253.50 rows=16 width=15) (actual time=0.094..0.094 rows=0 loops=1)
-> Index Scan using idx_tagdata_clientrmainid on tagrecord (cost=0.00..252.85 rows=195 width=15) (actual time=0.091..0.091 rows=0 loops=1)
Index Cond: ((clientrmainid)::text = 'CPC-RMA-00110'::text)
Total runtime: 0.146 ms
(4 rows)
tts_server_db=# EXPLAIN ANALYZE select distinct userdata from tagrecord where clientRmaInId = 'CPC-RMA-00110';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=260.27..261.25 rows=16 width=15) (actual time=0.115..0.115 rows=0 loops=1)
-> Sort (cost=260.27..260.76 rows=195 width=15) (actual time=0.113..0.113 rows=0 loops=1)
Sort Key: userdata
-> Index Scan using idx_tagdata_clientrmainid on tagrecord (cost=0.00..252.85 rows=195 width=15) (actual time=0.105..0.105 rows=0 loops=1)
Index Cond: ((clientrmainid)::text = 'CPC-RMA-00110'::text)
Total runtime: 0.151 ms
(6 rows)
For now we are stuck with 8.1, so the easiest fix for us is to use GROUP BY.
Since this is fixed in later versions, I guess there's not much to see here... :)
Thanks for the quick reply!
--
Dimi Paun <dimi@lattica.com>
Lattica, Inc.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-02-10 01:43 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-09 21:46 DISTINCT vs. GROUP BY Dimi Paun <dimi@lattica.com>
2010-02-09 22:22 ` Thom Brown <thombrown@gmail.com>
2010-02-09 22:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-02-10 01:43 ` Dimi Paun <dimi@lattica.com>
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