Mastering PostgreSQL 9.6

Investigating performance

Grouping sets are a powerful feature; they help to reduce the number of expensive queries. Internally, PostgreSQL will basically turn to traditional GroupAggregates to make things work. A GroupAggregate node requires sorted data, so be prepared that PostgreSQL might do a lot of temporary sorting:

test=# explain SELECT   region, country, avg(production)                                                                                         FROM    t_oil                                                                                                                                    WHERE   country IN ('USA', 'Canada', 'Iran', 'Oman')                                                                                             GROUP BY GROUPING SETS ( (), region, country);                             QUERY PLAN                                 ---------------------------------------------------------------  GroupAggregate  (cost=22.58..32.69 rows=34 width=52)    Group Key: region    Group Key: ()    Sort Key: country      Group Key: country    ->  Sort  (cost=22.58..23.04 rows=184 width=24)  Sort Key: ...