unnecessary sort in the execution plan when doing group by

Hi,

This is the Greenplum database 4.3.1.0.

Tables :

dev=# \d+ visits_weekly_new_3
Append-Only Columnar Table "uk.visits_weekly_new_3"
Column | Type | Modifiers | Storage | Compression Type | Compression Level | Block Size | Description
------------------+------------------------+-----------+----------+------------------+-------------------+------------+-------------
date | date | | plain | none | 0 | 32768 |
hw_id | character varying(256) | | extended | none | 0 | 32768 |
channel | character varying(256) | | extended | none | 0 | 32768 |
industries | integer[] | | extended | none | 0 | 32768 |
weighted_visits | double precision | | plain | none | 0 | 32768 |
projected_visits | double precision | | plain | none | 0 | 32768 |
Checksum: f
Child tables: visits_weekly_new_3_1_prt_1,
visits_weekly_new_3_1_prt_2,
visits_weekly_new_3_1_prt_3,
visits_weekly_new_3_1_prt_4,
visits_weekly_new_3_1_prt_5,
visits_weekly_new_3_1_prt_6,
visits_weekly_new_3_1_prt_7,
visits_weekly_new_3_1_prt_8,
visits_weekly_new_3_1_prt_9
Has OIDs: no
Options: appendonly=true, orientation=column
Distributed by: (date, channel)

dev=# \d+ temp.tmp_hw_channel
Table "temp.tmp_hw_channel"
Column | Type | Modifiers | Storage | Description
--------+------------------------+-----------+----------+-------------
id | character varying(256) | | extended |
Has OIDs: no
Distributed by: (id)

Below is the execution plan for two SQL, the only difference between two SQL is that one has 2 group by columns and the other one has 3 group by columns. However, one is use hash aggregate, the other is doing sorting and group aggregate. It leads to very different performance although it has the same result set.

dev=# explain ANALYZE

SELECT v.date,

channel,

SUM(weighted_visits) AS weighted_visits,

SUM(projected_visits) AS projected_visits

FROM visits_weekly_new_3 v

INNER JOIN temp.tmp_hw_channel id ON v.hw_id = id.id

WHERE v.date >= '2014-05-03'

AND v.date<= '2014-05-24'

GROUP BY v.date,

channel;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 24:1 (slice2; segments: 24) (cost=31286842.08..31287447.81 rows=1683 width=536)
Rows out: 15380160 rows at destination with 14860 ms to first row, 23856 ms to end, start offset by 104 ms.
-> HashAggregate (cost=31286842.08..31287447.81 rows=1683 width=536)
Group By: v.date, v.channel
Rows out: Avg 640840.0 rows x 24 workers. Max 642307 rows (seg14) with 18979 ms to first row, 19365 ms to end, start offset by 57 ms.
Executor memory: 66688K bytes avg, 66794K bytes max (seg0).
-> Hash Join (cost=299802.88..28414086.88 rows=11969814 width=132)
Hash Cond: v.hw_id::text = id.id::text
Rows out: Avg 6657725.2 rows x 24 workers. Max 7363985 rows (seg10) with 1225 ms to first row, 18839 ms to end, start offset by 63 ms.
Executor memory: 35037K bytes avg, 35037K bytes max (seg0).
Work_mem used: 35037K bytes avg, 35037K bytes max (seg0). Workfile: (0 spilling, 0 reused)
(seg10) Hash chain length 1.3 avg, 7 max, using 389733 of 1048589 buckets.
-> Append (cost=0.00..5297308.80 rows=11969814 width=87)
Rows out: Avg 11969813.7 rows x 24 workers. Max 13482240 rows (seg10) with 1.284 ms to first row, 8168 ms to end, start offset by 1287 ms.
-> Append-only Columnar Scan on visits_weekly_new_3_1_prt_1 v (cost=0.00..1324327.20 rows=2992454 width=87)
Filter: date >= '2014-05-03'::date AND date <= '2014-05-24'::date
Rows out: Avg 2992453.4 rows x 24 workers. Max 3623583 rows (seg21) with 1.232 ms to first row, 1299 ms to end, start offset by 1279 ms.
-> Append-only Columnar Scan on visits_weekly_new_3_1_prt_2 v (cost=0.00..1324327.20 rows=2992454 width=87)
Filter: date >= '2014-05-03'::date AND date <= '2014-05-24'::date
Rows out: Avg 2992453.4 rows x 24 workers. Max 3767678 rows (seg10) with 0.312 ms to first row, 2123 ms to end, start offset by 5966 ms.
-> Append-only Columnar Scan on visits_weekly_new_3_1_prt_3 v (cost=0.00..1324328.20 rows=2992454 width=87)
Filter: date >= '2014-05-03'::date AND date <= '2014-05-24'::date
Rows out: Avg 2992453.4 rows x 24 workers. Max 4283207 rows (seg15) with 0.295 ms to first row, 1444 ms to end, start offset by 9383 ms.
-> Append-only Columnar Scan on visits_weekly_new_3_1_prt_4 v (cost=0.00..1324326.20 rows=2992454 width=87)
Filter: date >= '2014-05-03'::date AND date <= '2014-05-24'::date
Rows out: Avg 2992453.4 rows x 24 workers. Max 3760361 rows (seg12) with 0.299 ms to first row, 1309 ms to end, start offset by 14026 ms.
-> Hash (cost=127888.98..127888.98 rows=487373 width=45)
Rows in: Avg 487556.0 rows x 24 workers. Max 487556 rows (seg0) with 1188 ms to end, start offset by 86 ms.
-> Broadcast Motion 24:24 (slice1; segments: 24) (cost=0.00..127888.98 rows=487373 width=45)
Rows out: Avg 487556.0 rows x 24 workers at destination. Max 487556 rows (seg0) with 0.094 ms to first row, 590 ms to end, start offset by 86 ms.
-> Seq Scan on tmp_hw_channel id (cost=0.00..6045.73 rows=20308 width=45)
Rows out: Avg 20314.8 rows x 24 workers. Max 20536 rows (seg23) with 0.131 ms to first row, 6.642 ms to end, start offset by 69 ms.
Slice statistics:
(slice0) Executor memory: 286K bytes.
(slice1) Executor memory: 774K bytes avg x 24 workers, 774K bytes max (seg0).
(slice2) Executor memory: 149541K bytes avg x 24 workers, 149658K bytes max (seg0). Work_mem: 35037K bytes max.
Statement statistics:
Memory used: 1048576K bytes
Settings: enable_bitmapscan=on; enable_indexscan=on; enable_sort=off
Total runtime: 25374.000 ms
(40 rows)

Time: 25383.704 ms

dev=# explain ANALYZE
SELECT v.date,
channel,
industries,
SUM(weighted_visits) AS weighted_visits,
SUM(projected_visits) AS projected_visits
FROM visits_weekly_new_3 v
INNER JOIN temp.tmp_hw_channel id ON v.hw_id = id.id
WHERE v.date >= '2014-05-03'
AND v.date<= '2014-05-24'
GROUP BY v.date,
channel,
industries;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Gather Motion 24:1 (slice2; segments: 24) (cost=152269717.33..157009763.41 rows=1196982 width=568)
Rows out: 15380160 rows at destination with 35320 ms to first row, 70091 ms to end, start offset by 102 ms.
-> GroupAggregate (cost=152269717.33..157009763.41 rows=1196982 width=568)
Group By: v.date, v.channel, v.industries
Rows out: Avg 640840.0 rows x 24 workers. Max 642307 rows (seg14) with 48843 ms to first row, 54853 ms to end, start offset by 54 ms.
-> Sort (cost=152269717.33..152987906.13 rows=11969814 width=155)
Sort Key: v.date, v.channel, v.industries
Rows out: Avg 6657725.2 rows x 24 workers. Max 7363985 rows (seg10) with 64604 ms to first row, 65912 ms to end, start offset by 62 ms.
Executor memory: 692755K bytes avg, 760338K bytes max (seg15).
Work_mem used: 692755K bytes avg, 760338K bytes max (seg15). Workfile: (24 spilling, 0 reused)
Work_mem wanted: 1603070K bytes avg, 1782291K bytes max (seg10) to lessen workfile I/O affecting 24 workers.
-> Hash Join (cost=299802.88..28834900.88 rows=11969814 width=155)
Hash Cond: v.hw_id::text = id.id::text
Rows out: Avg 6657725.2 rows x 24 workers. Max 7363985 rows (seg10) with 1226 ms to first row, 24249 ms to end, start offset by 62 ms.
Executor memory: 35037K bytes avg, 35037K bytes max (seg0).
Work_mem used: 35037K bytes avg, 35037K bytes max (seg0). Workfile: (0 spilling, 0 reused)
(seg10) Hash chain length 1.3 avg, 7 max, using 389733 of 1048589 buckets.
(seg15) Hash chain length 1.3 avg, 7 max, using 389733 of 1048589 buckets.
-> Append (cost=0.00..5297308.80 rows=11969814 width=111)
Rows out: Avg 11969813.7 rows x 24 workers. Max 13482240 rows (seg10) with 0.846 ms to first row, 11214 ms to end, start offset by 1287 ms.
-> Append-only Columnar Scan on visits_weekly_new_3_1_prt_1 v (cost=0.00..1324327.20 rows=2992454 width=111)
Filter: date >= '2014-05-03'::date AND date <= '2014-05-24'::date
Rows out: Avg 2992453.4 rows x 24 workers. Max 3623583 rows (seg21) with 0.624 ms to first row, 1465 ms to end, start offset by 1264 ms.
-> Append-only Columnar Scan on visits_weekly_new_3_1_prt_2 v (cost=0.00..1324327.20 rows=2992454 width=110)
Filter: date >= '2014-05-03'::date AND date <= '2014-05-24'::date
Rows out: Avg 2992453.4 rows x 24 workers. Max 3767678 rows (seg10) with 0.486 ms to first row, 2419 ms to end, start offset by 8616 ms.
-> Append-only Columnar Scan on visits_weekly_new_3_1_prt_3 v (cost=0.00..1324328.20 rows=2992454 width=111)
Filter: date >= '2014-05-03'::date AND date <= '2014-05-24'::date
Rows out: Avg 2992453.4 rows x 24 workers. Max 4283207 rows (seg15) with 0.453 ms to first row, 2357 ms to end, start offset by 13242 ms.
-> Append-only Columnar Scan on visits_weekly_new_3_1_prt_4 v (cost=0.00..1324326.20 rows=2992454 width=110)
Filter: date >= '2014-05-03'::date AND date <= '2014-05-24'::date
Rows out: Avg 2992453.4 rows x 24 workers. Max 3760361 rows (seg12) with 0.440 ms to first row, 2532 ms to end, start offset by 35558 ms.
-> Hash (cost=127888.98..127888.98 rows=487373 width=45)
Rows in: Avg 487556.0 rows x 24 workers. Max 487556 rows (seg0) with 1184 ms to end, start offset by 74 ms.
-> Broadcast Motion 24:24 (slice1; segments: 24) (cost=0.00..127888.98 rows=487373 width=45)
Rows out: Avg 487556.0 rows x 24 workers at destination. Max 487556 rows (seg0) with 0.168 ms to first row, 622 ms to end, start offset by 74 ms.
-> Seq Scan on tmp_hw_channel id (cost=0.00..6045.73 rows=20308 width=45)
Rows out: Avg 20314.8 rows x 24 workers. Max 20536 rows (seg23) with 0.263 ms to first row, 6.508 ms to end, start offset by 70 ms.
Slice statistics:
(slice0) Executor memory: 286K bytes.
(slice1) Executor memory: 774K bytes avg x 24 workers, 774K bytes max (seg0).
(slice2) * Executor memory: 771617K bytes avg x 24 workers, 843298K bytes max (seg15). Work_mem: 760338K bytes max, 1782291K bytes wanted.
Statement statistics:
Memory used: 1048576K bytes
Memory wanted: 3565580K bytes
Settings: enable_bitmapscan=on; enable_indexscan=on; enable_sort=off
Total runtime: 72071.845 ms
(47 rows)

Time: 72078.079 ms