Why could PostgreSQL 9.5's CUBE, ROLLUP and GROUPING SETS be slower than equivalent UNION?

Question

I've anticipated new PostgreSQL 9.5 features very much, and going to upgrade our database very soon. But I was quite surprised when I found that

SELECT col1, col2, count(*), grouping(col1,col2) 
FROM table1 
GROUP BY CUBE(col1, col2)

query on our dataset actually runs much slower (~3 seconds) than sum of durations of queries for equivalent data (~1second total for all 4 queries, 100-300ms each). Both col1 and col2 have indexes on them.

Is this expected (meaning that feature is more about compatibility than about performance right now)? Or could it be tweaked somehow?

Here's an example on vacuumed production table:

> explain analyze select service_name, state, res_id, count(*) from bookings group by rollup(service_name, state, res_id);
                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=43069.12..45216.05 rows=4161 width=24) (actual time=1027.341..1120.675 rows=428 loops=1)
   Group Key: service_name, state, res_id
   Group Key: service_name, state
   Group Key: service_name
   Group Key: ()
   ->  Sort  (cost=43069.12..43490.18 rows=168426 width=24) (actual time=1027.301..1070.321 rows=168426 loops=1)
         Sort Key: service_name, state, res_id
         Sort Method: external merge  Disk: 5728kB
         ->  Seq Scan on bookings  (cost=0.00..28448.26 rows=168426 width=24) (actual time=0.079..147.619 rows=168426 loops=1)
 Planning time: 0.118 ms
 Execution time: 1122.557 ms
(11 rows)

> explain analyze select service_name, state, res_id, count(*) from bookings group by service_name, state, res_id
UNION ALL select service_name, state, NULL, count(*) from bookings group by service_name, state
UNION ALL select service_name, NULL, NULL, count(*) from bookings group by service_name
UNION ALL select NULL, NULL, NULL, count(*) from bookings;
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Append  (cost=30132.52..118086.91 rows=4161 width=32) (actual time=208.986..706.347 rows=428 loops=1)
   ->  HashAggregate  (cost=30132.52..30172.12 rows=3960 width=24) (actual time=208.986..209.078 rows=305 loops=1)
         Group Key: bookings.service_name, bookings.state, bookings.res_id
         ->  Seq Scan on bookings  (cost=0.00..28448.26 rows=168426 width=24) (actual time=0.022..97.637 rows=168426 loops=1)
   ->  HashAggregate  (cost=29711.45..29713.25 rows=180 width=20) (actual time=195.851..195.879 rows=96 loops=1)
         Group Key: bookings_1.service_name, bookings_1.state
         ->  Seq Scan on bookings bookings_1  (cost=0.00..28448.26 rows=168426 width=20) (actual time=0.029..95.588 rows=168426 loops=1)
   ->  HashAggregate  (cost=29290.39..29290.59 rows=20 width=11) (actual time=181.955..181.960 rows=26 loops=1)
         Group Key: bookings_2.service_name
         ->  Seq Scan on bookings bookings_2  (cost=0.00..28448.26 rows=168426 width=11) (actual time=0.030..97.047 rows=168426 loops=1)
   ->  Aggregate  (cost=28869.32..28869.33 rows=1 width=0) (actual time=119.332..119.332 rows=1 loops=1)
         ->  Seq Scan on bookings bookings_3  (cost=0.00..28448.26 rows=168426 width=0) (actual time=0.039..93.508 rows=168426 loops=1)
 Planning time: 0.373 ms
 Execution time: 706.558 ms
(14 rows)

total time is comparable, but latter uses four scans, shouldn't it be slower? "external merge on Disk" while using rollup() is strange, I have work_mem set to 16M.

Show us the execution plans using `explain (analyze, verbose)` — , Feb 04 '16 at 20:24
added an example. CUBE() on the same columns gives even more drastic difference — codesnik, Feb 22 '16 at 02:29
The sort (external merge sort) takes most of the time, right? 1027+ ms, or am I misreading this? — Mike Sherrill 'Cat Recall', Feb 22 '16 at 02:38
In https://www.postgresql.org/message-id/54f48e4f0905100640l3ece5a3x6b6c3e7e3a91d260@mail.gmail.com, I see "This is suboptimal. When SELECT * FROM X GROUP BY GROUPING SETS ..., where X is some joined data, then you repeat JOIN on every grouping set. So your solution is simple for implementation, but it should be really slow." - Not sure if you have joins though... — Nona, Dec 16 '16 at 01:27
@Nona I had them when I stumbled upon a problem, but I dropped them while trying to pinpoint the issue. It looked like it's still suboptimal. — codesnik, Dec 17 '16 at 19:33

codesnik · Answer 1 · 2016-02-22T03:00:38.427

1

Interesting, but in that particular example SET work_mem='32mb' gets rid of disk merge, and now using ROLLUP is 2x faster than corresponding union.

explain analyze now contains: "Sort Method: quicksort Memory: 19301kB"

I still wonder why so much memory needed for mere 400 rows of output, and why disk merge needed 7Mb compared to 19Mb of memory (quicksort overhead?), but my problem is solved.

edited Feb 22 '16 at 03:00

answered Feb 22 '16 at 02:38

codesnik

299
2
7

yeah, you're right. And that's the whole table! Does it mean that ROLLUP/CUBE/GROUPING SETS can only work in such a (more or less) naive way, or are there extreme cases when it makes sense? – codesnik Feb 22 '16 at 03:04

score 0 · Answer 2 · answered May 25 '16 at 13:03

0

Its seems that grouping sets always have GroupAggregate with Sort in query plan. But standard group by frequency use HashAggragate.

answered May 25 '16 at 13:03

oscavi

347
3
4

Why could PostgreSQL 9.5's CUBE, ROLLUP and GROUPING SETS be slower than equivalent UNION?

2 Answers2