Mastering PostgreSQL 12 - Third Edition

Investigating performance

Grouping sets is a powerful feature; they help to reduce the number of expensive queries. Internally, PostgreSQL will basically use MixedAggregate to perform the aggregation. It can perform many operations at once, which ensures efficiency, as shown in the following example:

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 -------------------------------------------------------------------- MixedAggregate (cost=0.00..18.17 rows=17 width=52)   Hash Key: region   Hash Key: country   Group Key: ()   -> Seq Scan on t_oil (cost=0.00..15.66 rows=184 width=24) Filter: (country = ANY ('{USA,Canada,Iran,Oman}'::text[])) ...