GROUPING SETS, ROLLUP, CUBE: many groupings, one pass

One query, several groupings

Ask yourself: how many times have you seen a dashboard built from three near-identical queries glued with UNION ALL, each scanning the same table and drifting out of sync the moment someone fixes a bug in one branch? GROUPING SETS is the answer.

Normally a GROUP BY produces exactly one grouping. GROUPING SETS lets you ask for several groupings in a single query, stacking their results into one output:

SELECT status, user_id, SUM(total) AS revenue
FROM orders
GROUP BY GROUPING SETS (
  (status),        -- revenue per status
  (user_id),       -- revenue per user
  ()               -- grand total (empty set = no grouping)
);

Each parenthesized list is one grouping. The empty set () means “don’t group at all” — the grand total over every row. Postgres scans orders once and emits the union of all three groupings. Columns not part of a given grouping come back as NULL in those rows (the user_id is NULL in the per-status rows, because user isn’t in that set).

ROLLUP and CUBE: shorthands for common shapes

Two shorthands cover the patterns you actually want:

• ROLLUP (a, b) expands to the hierarchical grouping sets (a, b), (a), (). Use it when columns form a hierarchy — (year, month) → totals per month, subtotal per year, grand total. It’s “drill-down with subtotals.”
• CUBE (a, b) expands to all combinations: (a, b), (a), (b), (). Use it for a full cross-tab where every dimension and the totals matter.

-- Subtotals per status within the rollup hierarchy, plus a grand total:
SELECT status, SUM(total) AS revenue
FROM orders
GROUP BY ROLLUP (status);   -- = GROUPING SETS ((status), ())

-- Every combination of status × user_id, plus all subtotals & grand total:
SELECT status, user_id, SUM(total)
FROM orders
GROUP BY CUBE (status, user_id);

ROLLUP (a, b) produces n+1 grouping sets; CUBE of n columns produces 2^n. Reach for ROLLUP for hierarchies (the common 90%) and CUBE only when you genuinely need every cross-combination — its output grows exponentially with dimensions.

The reason this beats the old UNION ALL pattern is the scan count. On a 12M-row orders table, computing per-status, per-user, and grand-total as three SELECTs glued with UNION ALL is three independent Seq Scans — ~3 × 800ms ≈ 2.4s plus the cost of re-reading 36M rows total. The GROUPING SETS form EXPLAIN ANALYZEs as a single Seq Scan (rows=12000000) feeding a MixedAggregate (or GroupAggregate over a sorted input) that computes all three groupings from one pass — ~950ms, one read of 12M rows. CUBE’s catch is the output: CUBE over 4 dimensions is 2⁴ = 16 grouping sets, and on high-cardinality columns each set can be millions of rows — the result can dwarf the base table. Postgres still does it in one input scan, but the aggregation state for 16 simultaneous groupings can blow work_mem and spill (HashAggregate ... Disk Usage), so add dimensions to a CUBE deliberately.

GROUPING(): is this NULL a subtotal or real data?

Here’s the trap. A subtotal row has NULL in the columns it’s not grouped by. But your data might also contain a genuine NULL status. How do you tell “all statuses” (subtotal) from “the order whose status is actually NULL”? The GROUPING() function:

SELECT
  status,
  SUM(total) AS revenue,
  GROUPING(status) AS is_subtotal   -- 1 = subtotal row, 0 = real status value
FROM orders
GROUP BY ROLLUP (status);

GROUPING(col) returns 1 if col was rolled up (aggregated away) for that row — i.e. this is a subtotal/total row — and 0 if col is a real grouped value (including a genuine NULL). Use it to label rows or to coalesce cleanly: CASE WHEN GROUPING(status) = 1 THEN 'All statuses' ELSE status END. Without GROUPING(), a real NULL status and a subtotal are indistinguishable, and your report silently double-labels them.