GROUPING SETS, ROLLUP and MixedAggregate Plans #

A dashboard computes totals by region, by product, by region and product, and a grand total in one statement with GROUPING SETS. The plan shows a single MixedAggregate node with three Hash Key lines and one Group Key. It runs in 14 seconds; four separate GROUP BY queries take 5 seconds together. Multi-level grouping in one pass should be cheaper, and often is — but only when the planner can share work between the levels.

The basic choice between sorted and hashed grouping is described in GroupAggregate vs HashAggregate selection. Grouping sets combine several of those choices in one node.

The Planner Condition #

GROUPING SETS ((region), (product), (region, product), ()) asks for four groupings of the same input. ROLLUP (a, b, c) is shorthand for the prefixes (a,b,c), (a,b), (a), (); CUBE (a, b) for every subset. The planner chooses among:

The decision uses the estimated number of groups for each set, the hash memory budget, and whether the input already arrives sorted — from an index or a lower merge join. Poor group estimates for any one set can push the whole node into a bad mix.

One pass, several grouping sets A stack from input to outputs. The input scan feeds MixedAggregate. Inside, hash tables serve the region and the product grouping sets, while the sorted rollup path serves region plus product and the grand total. All four sets are emitted by the same node. Seq Scan on sales one pass over the input MixedAggregate single node, several strategies Hash Key: region hash table 1 Hash Key: product hash table 2 Group Key: (region, product) → () sorted rollup, needs order memory = all hash tables at once; sorts add their own work

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT region, product, sum(amount)
FROM sales
WHERE sold_on >= '2026-01-01'
GROUP BY GROUPING SETS ((region), (product), (region, product), ());
MixedAggregate  (actual time=13904.2..14102.6 rows=412050 loops=1)
  Hash Key: region
  Hash Key: product
  Group Key: region, product
  Group Key: ()
  Batches: 1  Memory Usage: 18424kB
  ->  Sort  (actual time=11204.6..12910.8 rows=62040110 loops=1)
        Sort Key: region, product
        Sort Method: external merge  Disk: 2104412kB          -- 2 GB sort for one rollup chain
        ->  Seq Scan on sales  (actual rows=62040110 loops=1)
              Filter: (sold_on >= '2026-01-01'::date)
Execution Time: 14210.8 ms
-- the (region, product) set has 412k groups: planner chose to sort for it
-- the two single-column sets are hashed alongside in 18 MB

With enough hash memory for every set:

BEGIN;
SET LOCAL hash_mem_multiplier = 8;
EXPLAIN (ANALYZE, BUFFERS) <same query>;
ROLLBACK;
HashAggregate  (actual time=6804.2..6980.4 rows=412050 loops=1)
  Hash Key: region, product
  Hash Key: region
  Hash Key: product
  Group Key: ()
  Batches: 1  Memory Usage: 94402kB
  ->  Seq Scan on sales  (actual rows=62040110 loops=1)
Execution Time: 7010.2 ms
-- no sort at all: every set hashed, 94 MB total
Reading the grouping-set strategy lines Four lines are annotated. Hash Key region and Hash Key product show two sets served by hash tables. Group Key region, product shows a sort-based rollup that needs ordered input. The Sort below with external merge shows that ordering cost 2 gigabytes of disk. Hash Key: region · Hash Key: product two hash tables, one pass Group Key: region, product sorted set, needs ordered input Group Key: () grand total, no hash table Sort Method: external merge Disk: 2104412kB the price of the sorted set each Hash Key is its own table; each distinct sort order is its own sort

Step-by-Step Resolution #

  1. List the strategy for each set from the Hash Key and Group Key lines. Note every Sort beneath or inside the node.

  2. Check group estimates per set. Run the equivalent single GROUP BY with EXPLAIN for the largest set and compare rows= with reality; a large underestimate sends that set to hashing that later spills, an overestimate sends it to an expensive sort.

  3. Test with more hash memory for the transaction, as above. If every set fits, a pure HashAggregate avoids sorts entirely — sized with hash_mem_multiplier and the hash memory budget.

  4. Or provide the sort order from an index so sorted sets are free:

    CREATE INDEX CONCURRENTLY sales_sold_region_product_idx
  ON sales (region, product) WHERE sold_on >= '2026-01-01';

    A partial index is only reusable if the query’s range matches; otherwise index the full columns.

  5. Prefer ROLLUP shapes that nest along one order when the report allows it: one sort then serves every level.

  6. Precompute the finest grouping into a summary table and derive coarser levels from it with GROUPING SETS over far fewer rows.

Cost structure of common grouping shapes A table of grouping forms. ROLLUP of region and product needs one sort or one hash table per level. CUBE of region and product needs two sort orders or four hash tables. Arbitrary GROUPING SETS need one sort per incompatible order. Precomputing the finest level shrinks the input for every form. sorted path hashed path ROLLUP (region, product) 1 sort, all levels 2 tables + total CUBE (region, product) 2 sort orders 3 tables + total GROUPING SETS (a), (b) 2 sort orders 2 tables finest level precomputed tiny sorts tiny tables sets that share a prefix order share one sort

Before and After #

-- BEFORE: MixedAggregate with 2 GB external sort for (region, product)
MixedAggregate → Sort (external merge  Disk: 2104412kB) → Seq Scan        Execution Time: 14210.8 ms

-- AFTER: SET LOCAL hash_mem_multiplier = 8 for the dashboard transaction
HashAggregate  Hash Key × 3  Batches: 1  Memory Usage: 94402kB → Seq Scan   Execution Time: 7010.2 ms

Why separate queries were faster #

The four separate queries each chose their best strategy independently — three hash aggregates and a plain count — and three of them had tiny hash tables. The grouping-sets query instead committed to one node for all sets, and the planner judged the largest set’s hash table too big for the budget in force, so it paid for a full sort to serve it. Once the budget allowed all tables in memory, the single-pass plan beat the separate queries, because it read the 62 million input rows once instead of four times. The general lesson: grouping sets save input passes, but they bind all sets to one memory budget and one node. When one set is much larger than the others, give that node the memory it needs, or split that set into its own query.

The GROUPING() function helps tell levels apart in the output — GROUPING(region, product) returns a bitmask of which columns are aggregated away — and costs almost nothing to compute, so use it rather than inferring levels from nulls, which are ambiguous when the data itself contains nulls.

Common Pitfalls #

Assuming one pass means one cheap node. Each incompatible sort order is another full sort. Diagnostic signal: several Sort Key entries or a large sort beneath the node. Fix: nest sets as a rollup, add memory, or index the order.

Hash memory summed across sets. Every hashed set holds its table simultaneously. Diagnostic signal: Memory Usage equal to the sum of all group counts. Fix: budget the total, not the largest set.

Mistaking nulls for totals. Rows where a column is genuinely null look like subtotal rows. Diagnostic signal: double-counted subtotals in reports. Fix: use GROUPING() to identify levels.

CUBE over many columns. The number of sets doubles with each column. Diagnostic signal: dozens of Hash Key lines. Fix: request only the sets the report displays.

Frequently Asked Questions #

What is MixedAggregate in PostgreSQL? #

It is an aggregate node that computes some grouping sets with hash tables and others with a sort-based rollup, in a single pass over its input. The plan lists hashed sets as Hash Key lines and sorted sets as Group Key lines.

Are GROUPING SETS faster than separate GROUP BY queries? #

They read the input once instead of once per grouping, which usually wins. They can lose when one large set forces an expensive sort or spill because all sets share one node and one memory budget.

How many sorts does ROLLUP need? #

One, because every level of ROLLUP (a, b, c) is a prefix of the order (a, b, c). CUBE and arbitrary grouping sets need an additional sort for each order that is not a prefix of another.

Up: Aggregate and Grouping Strategies