ROLLUP and CUBE for hierarchical totals in PostgreSQL - Time & Space Complexity

Time Complexity: ROLLUP and CUBE for hierarchical totals

O(n log n)

Understanding Time Complexity

When using ROLLUP and CUBE in SQL, we want to know how the time to get results changes as the data grows.

We ask: How does adding more data or grouping columns affect the work the database does?

Scenario Under Consideration

Analyze the time complexity of this SQL query using ROLLUP.

SELECT region, product, SUM(sales) AS total_sales
FROM sales_data
GROUP BY ROLLUP(region, product);
    

This query calculates sales totals for each region and product, plus subtotals and a grand total.

Identify Repeating Operations

Look for repeated work done by the database.

• Primary operation: Scanning all rows in the sales_data table to sum sales.
• How many times: Once for the full data, but grouping creates multiple subtotal calculations.

How Execution Grows With Input

As the number of rows (n) grows, the database must process more data to sum.

Input Size (n)	Approx. Operations
10	About 10 sums plus grouping overhead
100	About 100 sums plus grouping overhead
1000	About 1000 sums plus grouping overhead

Pattern observation: The work grows roughly in direct proportion to the number of rows.

Final Time Complexity

Time Complexity: O(n log n)

This means the time to run the query grows roughly in proportion to n log n as the data size increases, due to grouping overhead.

Common Mistake

[X] Wrong: "ROLLUP or CUBE makes the query take exponentially longer because it does many groupings."

[OK] Correct: The database efficiently computes subtotals during one pass, so the main cost is still scanning all rows once plus grouping overhead.

Interview Connect

Understanding how grouping with ROLLUP or CUBE affects performance helps you explain query costs clearly and shows you know how databases handle summaries.

Self-Check

"What if we add more columns to the ROLLUP list? How would that change the time complexity?"