<p><code>GROUP BY</code> and window functions look declarative — the query says what it wants, and PostgreSQL figures out how to compute it. In practice the planner has strong opinions about <em>how</em>: whether to hash or sort, whether to parallelise, whether to spill memory to disk, whether a matching index changes the plan entirely. Learn to read what the planner picked and why, and aggregate-heavy queries become one of the easiest categories to tune.</p> <p>This article is the fifth in the <a href="https://mydba.dev/blog/postgres-query-analysis-complete-guide" rel="noopener noreferrer">Complete Guide to PostgreSQL SQL Query Analysis &amp; Optimization</a> series. Every EXPLAIN block below is captured from a real run on the series' Neon Postgres 17.8 database (500,000-row <code>sim_bp_orders</code> and friends).</p> <h2> The two aggregate strategies </h2> <p>For <code>GROUP BY</code>, the planner chooses primarily between two implementations — plus some parallel and distinct variants layered on top.</p> <p><strong><code>HashAggregate</code></strong> builds a hash table keyed by the group-by columns; each incoming row probes the hash and either creates a new entry or updates an existing one's running aggregate state. Fast when the hash table fits in <code>work_mem</code>. Doesn't care about input order.</p> <p><strong><code>GroupAggregate</code></strong> requires input already sorted on the group-by columns. Each group's rows arrive contiguously, so the aggregate can emit a result row and clear its state between groups — constant memory regardless of group count. Picked when the input is already sorted (typically because the group-by matches an index order) or when the planner thinks the hash table won't fit.</p> <p>The distinguishing signal in EXPLAIN is the node type itself: <code>HashAggregate</code> vs <code>GroupAggregate</code>. When you see <code>Sort → GroupAggregate</code> and no matching index, the planner has decided a sort + streaming aggregate is cheaper than trying to hash. In parallel plans you'll often see a <em>composite</em> shape — <code>Partial HashAggregate</code> inside each worker, topped by <code>Finalize GroupAggregate</code> on the leader — which is a parallel partial-aggregation pattern rather than "just a HashAggregate."</p> <p>Here's that exact shape, from the classic dashboard query "how many orders in each status?":<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="k">SELECT</span> <span class="n">status</span><span class="p">,</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">),</span> <span class="k">avg</span><span class="p">(</span><span class="n">total_amount_cents</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">sim_bp_orders</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">status</span><span class="p">;</span> </code></pre> </div> <div class="highlight js-code-highlight"> <pre class="highlight plaintext"><code>Finalize GroupAggregate (cost=8334.96..8336.27 rows=5 width=49) (actual time=148.938..151.912 rows=5 loops=1) Group Key: sim_bp_orders.status Buffers: shared hit=3705 -&gt; Gather Merge (actual time=148.924..151.895 rows=15 loops=1) Workers Planned: 2 Workers Launched: 2 -&gt; Sort (actual time=140.390..140.391 rows=5 loops=3) Sort Key: sim_bp_orders.status Sort Method: quicksort Memory: 25kB -&gt; Partial HashAggregate (actual time=140.366..140.367 rows=5 loops=3) Group Key: sim_bp_orders.status Batches: 1 Memory Usage: 24kB -&gt; Parallel Seq Scan on sim_bp_orders (actual time=0.006..32.097 rows=166667 loops=3) Execution Time: 151.973 ms </code></pre> </div> <p>152 ms. This is <strong>parallel partial aggregation</strong>: each parallel worker (plus the leader, making three process loops) computes a partial HashAggregate over its slice of the table (<code>rows=166667 loops=3</code> ≈ 500k total), produces its five-row partial result, sorts those by <code>status</code>, and feeds them up to <code>Gather Merge</code>. The leader then finalises with <code>Finalize GroupAggregate</code> — combining the three sets of partial states into five final rows. Partial aggregation is the reason aggregate queries scale so well with parallel workers: only the partial group states (5 rows per worker here, 15 rows total) cross the worker-to-leader boundary, no matter how big the input was.</p> <p>The <code>Batches: 1 Memory Usage: 24kB</code> on the Partial HashAggregate means the hash table fit in <code>work_mem</code> and didn't spill. Five groups with running sum/count fits easily in 24 kB.</p> <h2> The aggregate spill — and how to diagnose it </h2> <p>Things get interesting when the number of groups grows. A HashAggregate spill on a 117k-group count looks like:<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight plaintext"><code>HashAggregate (actual time=347.354..392.138 rows=117060 loops=1) Group Key: u.email Planned Partitions: 4 Batches: 5 Memory Usage: 8241kB Disk Usage: 6920kB </code></pre> </div> <p>The <code>Disk Usage: 6920kB</code> and <code>Batches &gt; 1</code> are the spill signals. PostgreSQL 13+ handles this gracefully — the executor detects that not all groups fit in memory, writes partial state to per-partition spill files, and processes them in additional passes — but the extra I/O is not free. On our database it cost roughly 40% of the query's total time.</p> <p>Two fixes for HashAggregate spills:</p> <ol> <li> <strong>Raise <code>work_mem</code> per-session</strong> so the hash fits in memory. Set per-role (<code>ALTER ROLE analytics SET work_mem = '64MB'</code>) rather than cluster-wide, because <code>work_mem</code> is allocated per sort/hash node <em>per connection</em> and a cluster-wide raise multiplies by concurrency.</li> <li> <strong>Sort + GroupAggregate</strong> is cheaper than a spilling HashAggregate when the group-by column is indexed. Force it with <code>SET enable_hashagg = off;</code> as a diagnostic, and if the Sort + GroupAggregate plan is faster, the underlying issue is "too many groups for current work_mem." Usually the right answer is to raise <code>work_mem</code> for the session anyway, since Sort also uses <code>work_mem</code>.</li> </ol> <p>The MyDBA analyzer rule <code>temp_blocks_written</code> fires when a node's <code>Temp Written Blocks</code> exceeds 100. That field is populated from JSON-format EXPLAIN output — MyDBA's visualiser runs the rules over JSON plans, not the text format pasted here — so the rule fires automatically on both HashAggregate spills and Sort spills when captured through the native integration.</p> <h2> Sort spills — the external merge </h2> <p>When a sort doesn't fit in <code>work_mem</code>, PostgreSQL falls back to an external merge sort: write sorted runs to disk, then merge them. You see this as <code>Sort Method: external merge</code> with a <code>Disk:</code> size in the sort node:<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="k">SELECT</span> <span class="n">status</span><span class="p">,</span> <span class="n">percentile_cont</span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">5</span><span class="p">)</span> <span class="n">WITHIN</span> <span class="k">GROUP</span> <span class="p">(</span><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">total_amount_cents</span><span class="p">)</span> <span class="k">AS</span> <span class="n">median</span><span class="p">,</span> <span class="n">percentile_cont</span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">95</span><span class="p">)</span> <span class="n">WITHIN</span> <span class="k">GROUP</span> <span class="p">(</span><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">total_amount_cents</span><span class="p">)</span> <span class="k">AS</span> <span class="n">p95</span> <span class="k">FROM</span> <span class="n">sim_bp_orders</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">status</span><span class="p">;</span> </code></pre> </div> <p>Percentiles are expensive because the implementation needs an ordered sample per group. PostgreSQL's <code>percentile_cont</code> evaluates as an ordered-set aggregate, which requires sorting the input per group:<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight plaintext"><code>GroupAggregate (actual time=202.589..358.067 rows=5 loops=1) Group Key: status Buffers: shared hit=3697, temp read=2707 written=2494 -&gt; Sort (actual time=146.514..202.485 rows=500000 loops=1) Sort Key: status Sort Method: external merge Disk: 12048kB Buffers: shared hit=3689, temp read=1506 written=1512 -&gt; Seq Scan on sim_bp_orders (actual time=0.007..49.886 rows=500000 loops=1) Execution Time: 358.230 ms </code></pre> </div> <p>358 ms. The Sort spilled 12 MB of temp files. The <code>GroupAggregate</code> node above it shows its own <code>temp read=2707 written=2494</code> — that's the ordered-set aggregate's internal tuplestore materialising per-group sorted input for the percentile computation, not a generic "every aggregate spills" phenomenon. Ordered-set aggregates like <code>percentile_cont</code>, <code>percentile_disc</code>, and <code>mode()</code> all force per-group materialisation; a simple <code>count()</code> or <code>avg()</code> on the same plan wouldn't produce that second temp-I/O figure. The MyDBA rule <code>sort_on_disk</code> fires on any Sort with <code>Sort Space Type = Disk</code>, which this plan has.</p> <p>The right fix depends on the workload. For a one-off analytical report, raising <code>work_mem</code> to ~40 MB for that session turns the external merge into an in-memory quicksort. For a dashboard that runs this every minute, you want a materialised view:<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="k">CREATE</span> <span class="n">MATERIALIZED</span> <span class="k">VIEW</span> <span class="n">order_amount_percentiles</span> <span class="k">AS</span> <span class="k">SELECT</span> <span class="n">status</span><span class="p">,</span> <span class="n">percentile_cont</span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">5</span><span class="p">)</span> <span class="n">WITHIN</span> <span class="k">GROUP</span> <span class="p">(</span><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">total_amount_cents</span><span class="p">)</span> <span class="k">AS</span> <span class="n">median</span><span class="p">,</span> <span class="n">percentile_cont</span><span class="p">(</span><span class="mi">0</span><span class="p">.</span><span class="mi">95</span><span class="p">)</span> <span class="n">WITHIN</span> <span class="k">GROUP</span> <span class="p">(</span><span class="k">ORDER</span> <span class="k">BY</span> <span class="n">total_amount_cents</span><span class="p">)</span> <span class="k">AS</span> <span class="n">p95</span> <span class="k">FROM</span> <span class="n">sim_bp_orders</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">status</span><span class="p">;</span> <span class="c1">-- Refresh on whatever schedule fits your freshness requirement:</span> <span class="n">REFRESH</span> <span class="n">MATERIALIZED</span> <span class="k">VIEW</span> <span class="n">CONCURRENTLY</span> <span class="n">order_amount_percentiles</span><span class="p">;</span> </code></pre> </div> <p><code>REFRESH MATERIALIZED VIEW CONCURRENTLY</code> requires a unique index on the view, reads the source tables outside the refresh window, and replaces the view atomically. The dashboard then queries the view instead of re-running the percentile calculation, and the 358 ms query becomes a 0.5 ms single-row scan.</p> <h2> Window functions </h2> <p>A window function produces an output row for every input row, but with access to a <em>frame</em> of related rows. The syntax:<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="n">agg_func</span><span class="p">(...)</span> <span class="n">OVER</span> <span class="p">(</span> <span class="k">PARTITION</span> <span class="k">BY</span> <span class="n">col1</span><span class="p">,</span> <span class="n">col2</span> <span class="c1">-- split input into independent groups</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">col3</span><span class="p">,</span> <span class="n">col4</span> <span class="c1">-- order within each partition</span> <span class="k">ROWS</span> <span class="k">BETWEEN</span> <span class="p">...</span> <span class="k">AND</span> <span class="p">...</span> <span class="c1">-- or RANGE BETWEEN, or GROUPS BETWEEN</span> <span class="p">)</span> </code></pre> </div> <p>The planner implements window functions via a <code>WindowAgg</code> node that consumes an input ordered appropriately and emits one output row per input. If the input isn't already ordered, the planner inserts a <code>Sort</code> before the WindowAgg — which is often where the cost lives.</p> <p>Consider a common pattern: "the most recent order per user." Pre-PostgreSQL 15 the usual rewrite was:<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="k">SELECT</span> <span class="n">user_id</span><span class="p">,</span> <span class="n">order_id</span><span class="p">,</span> <span class="n">created_at</span> <span class="k">FROM</span> <span class="p">(</span> <span class="k">SELECT</span> <span class="n">user_id</span><span class="p">,</span> <span class="n">order_id</span><span class="p">,</span> <span class="n">created_at</span><span class="p">,</span> <span class="n">row_number</span><span class="p">()</span> <span class="n">OVER</span> <span class="p">(</span><span class="k">PARTITION</span> <span class="k">BY</span> <span class="n">user_id</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">created_at</span> <span class="k">DESC</span><span class="p">)</span> <span class="k">AS</span> <span class="n">rn</span> <span class="k">FROM</span> <span class="n">sim_bp_orders</span> <span class="p">)</span> <span class="n">t</span> <span class="k">WHERE</span> <span class="n">rn</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">LIMIT</span> <span class="mi">100</span><span class="p">;</span> </code></pre> </div> <p>The PostgreSQL 15+ optimisation for this is the <strong>WindowAgg Run Condition</strong> — the planner notices that <code>WHERE rn = 1</code> can be pushed into the WindowAgg, so it can stop computing row numbers for each partition as soon as <code>rn &gt; 1</code>:<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight plaintext"><code>Limit (actual time=0.093..0.525 rows=100 loops=1) Buffers: shared hit=305 -&gt; WindowAgg (actual time=0.092..0.518 rows=100 loops=1) Run Condition: (row_number() OVER (?) &lt;= 1) -&gt; Incremental Sort Sort Key: user_id, created_at DESC Presorted Key: user_id Full-sort Groups: 9 Sort Method: quicksort -&gt; Index Scan using idx_sim_bp_orders_user_id on sim_bp_orders (actual time=0.014..0.339 rows=302 loops=1) Execution Time: 0.543 ms </code></pre> </div> <p>0.54 ms. Two optimisations are visible:</p> <ol> <li> <strong><code>Run Condition: (row_number() OVER (?) &lt;= 1)</code></strong> — the WindowAgg stops producing rows for a partition once <code>rn</code> exceeds 1, so only the first row per user is computed. This lets the plan short-circuit once <code>LIMIT 100</code> is satisfied after only 302 input rows (not the full 500k).</li> <li> <strong><code>Incremental Sort</code> with <code>Presorted Key: user_id</code></strong> — the input arrives already sorted by <code>user_id</code> (from <code>idx_sim_bp_orders_user_id</code>), and the WindowAgg needs it sorted by <code>(user_id, created_at DESC)</code>. An Incremental Sort only sorts <em>within each <code>user_id</code> group</em> rather than globally, which costs drastically less memory and allows pipelined execution.</li> </ol> <p>Even so, a <code>LATERAL</code> join with <code>LIMIT 1</code> inside is often simpler and at least as fast for "top-N per group" with small N.</p> <h2> Frame specifications </h2> <p>Most window function work defaults to an implicit frame clause that trips people up. The rules:</p> <ul> <li> <strong>No <code>ORDER BY</code> clause</strong> → the frame defaults to <code>RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</code> — the whole partition. This is what you want for <code>sum()</code> or <code>avg()</code> over an entire partition.</li> <li> <strong><code>ORDER BY</code> clause present</strong> → the frame defaults to <code>RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</code> — the running total up to this row. This is what you want for running sums, but easy to get wrong.</li> <li> <strong>Ranking functions</strong> (<code>row_number()</code>, <code>rank()</code>, <code>dense_rank()</code>) — the frame is irrelevant because the function's result only depends on the ordering.</li> </ul> <p>A common mistake: computing a "running average over the last 7 rows" and getting a running average over all preceding rows because the frame clause was omitted. The fix is explicit:<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="k">avg</span><span class="p">(</span><span class="n">value</span><span class="p">)</span> <span class="n">OVER</span> <span class="p">(</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="nb">timestamp</span> <span class="k">ROWS</span> <span class="k">BETWEEN</span> <span class="mi">6</span> <span class="k">PRECEDING</span> <span class="k">AND</span> <span class="k">CURRENT</span> <span class="k">ROW</span> <span class="p">)</span> </code></pre> </div> <p><code>ROWS BETWEEN N PRECEDING AND CURRENT ROW</code> is a physical window of N+1 rows. <code>RANGE BETWEEN '7 days' PRECEDING AND CURRENT ROW</code> is a logical window based on the ORDER BY value — useful when timestamps aren't evenly spaced. <code>GROUPS BETWEEN N PRECEDING AND CURRENT ROW</code> (PostgreSQL 11+) treats ties as a single "group" and counts those.</p> <h2> LAG, LEAD, and first/last value </h2> <p>The navigation functions — <code>lag(x, n)</code>, <code>lead(x, n)</code>, <code>first_value(x)</code>, <code>last_value(x)</code> — let you reference rows offset from the current one. Classic use: detect state transitions.<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="k">SELECT</span> <span class="n">order_id</span><span class="p">,</span> <span class="n">status</span><span class="p">,</span> <span class="n">lag</span><span class="p">(</span><span class="n">status</span><span class="p">)</span> <span class="n">OVER</span> <span class="p">(</span><span class="k">PARTITION</span> <span class="k">BY</span> <span class="n">user_id</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">created_at</span><span class="p">)</span> <span class="k">AS</span> <span class="n">prev_status</span><span class="p">,</span> <span class="n">created_at</span> <span class="k">FROM</span> <span class="n">sim_bp_orders</span> <span class="k">WHERE</span> <span class="n">user_id</span> <span class="o">=</span> <span class="mi">42</span><span class="p">;</span> </code></pre> </div> <p>Each row gets the status of the user's <em>previous</em> order. The window can then be wrapped in a subquery or CTE to find "orders where the status changed":<br> </p> <div class="highlight js-code-highlight"> <pre class="highlight sql"><code><span class="k">WITH</span> <span class="n">ordered</span> <span class="k">AS</span> <span class="p">(</span> <span class="k">SELECT</span> <span class="n">order_id</span><span class="p">,</span> <span class="n">status</span><span class="p">,</span> <span class="n">created_at</span><span class="p">,</span> <span class="n">lag</span><span class="p">(</span><span class="n">status</span><span class="p">)</span> <span class="n">OVER</span> <span class="p">(</span><span class="k">PARTITION</span> <span class="k">BY</span> <span class="n">user_id</span> <span class="k">ORDER</span> <span class="k">BY</span> <span class="n">created_at</span><span class="p">)</span> <span class="k">AS</span> <span class="n">prev_status</span> <span class="k">FROM</span> <span class="n">sim_bp_orders</span> <span class="p">)</span> <span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">ordered</span> <span class="k">WHERE</span> <span class="n">prev_status</span> <span class="k">IS</span> <span class="k">DISTINCT</span> <span class="k">FROM</span> <span class="n">status</span><span class="p">;</span> </code></pre> </div> <p>Two performance notes. First, <code>last_value()</code> with a default frame is surprising — because the default frame ends at the current row, <code>last_value()</code> returns the current row's value, not the partition's last. To actually get the partition's last value, specify <code>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING</code>. Second, LAG and LEAD compile to very cheap operations (just a pointer to the previous/next row in the window), while first_value/last_value with an explicit full-partition frame can force materialisation.</p> <h2> Aggregate-related indexes </h2> <p>An index on the GROUP BY columns is a straightforward win when it exists: the planner can use <code>GroupAggregate</code> over an index scan and skip the hash build entirely. The index has to cover the group key in <em>exactly</em> the right order — a composite index on <code>(status, created_at)</code> serves <code>GROUP BY status</code>, but a <code>(created_at, status)</code> doesn't.</p> <p>For queries that frequently aggregate a narrow window of a big table (<code>WHERE created_at &gt; ... GROUP BY user_id</code>), a partial index or materialised view of the aggregate result is usually the right answer, because re-aggregating millions of rows every time beats out any planner optimisation. Precomputation is the most robust performance tactic for aggregates.</p> <h2> Quick diagnostic checklist </h2> <p>When an aggregate query is slow:</p> <ol> <li> <strong>Is the aggregate node a <code>HashAggregate</code> with <code>Batches &gt; 1</code> or <code>Disk Usage &gt; 0</code>?</strong> The hash table spilled. Raise <code>work_mem</code> for the session, or create a supporting index to enable GroupAggregate instead.</li> <li> <strong>Is there a <code>Sort</code> above a <code>GroupAggregate</code> with <code>Sort Method: external merge</code>?</strong> The sort spilled. Same fix: more <code>work_mem</code>, or an index that provides pre-sorted input.</li> <li> <strong>Is there a <code>WindowAgg</code> over a <code>Sort</code> that processes all input before the <code>LIMIT</code>?</strong> Check if a <code>Run Condition</code> is possible (PG 15+) or if the problem can be rewritten as <code>LATERAL + LIMIT N</code>.</li> <li> <strong>Is the aggregate running every time the dashboard loads?</strong> Move it behind a materialised view refreshed on schedule. This is usually the biggest win of all.</li> <li> <strong>Does the MyDBA analyzer flag <code>sort_on_disk</code>, <code>hash_batches_spill</code>, or <code>temp_blocks_written</code>?</strong> These are the three rules that specifically target aggregate-related spills; if any fire, follow the suggestion inline.</li> </ol> <h2> Next steps </h2> <p>Aggregates interact closely with the shape of your WHERE clauses — a filter that narrows the input set before aggregation is almost always cheaper than aggregating and then filtering. The next article, <a href="https://mydba.dev/blog/postgres-where-clause-optimization" rel="noopener noreferrer">WHERE Clause Optimisation</a>, covers sargability and composite-index ordering in detail, with an eye toward getting predicates to apply as early in the plan as possible.</p> <h1> postgres #performance #database #sql </h1> <p>Full article with the complete series: <a href="https://mydba.dev/blog/postgres-aggregate-window-tuning" rel="noopener noreferrer">https://mydba.dev/blog/postgres-aggregate-window-tuning</a></p>