GroupAggregate vs HashAggregate Selection #

The planner does not prefer one aggregate strategy over another. It costs both, adds whatever is needed to make each legal — a Sort for the grouped path, a memory allowance for the hashed one — and takes the cheaper total. Understanding which term dominates lets you move the decision deliberately instead of by trial and error.

For the mechanics of each operator, start with aggregate and grouping strategies; this page is about the comparison itself.

The Cost Terms Being Compared #

Simplified to what actually moves, the planner weighs:

HashAggregate = input scan cost + (input rows × per-row hash and transition cost) + spill penalty when the estimated table exceeds work_mem × hash_mem_multiplier.

GroupAggregate = input scan cost + ordering cost + (input rows × per-row transition cost).

The “ordering cost” is the swing term, and it takes one of two values. If an index already delivers the grouping order, it is essentially zero and the sorted path usually wins. If a Sort node is required, it is roughly n log n over the whole input plus any temporary I/O when the sort exceeds work_mem — often the largest single number in the plan.

Two secondary effects matter in real queries:

Where the cost goes in each strategy Three stacked bars. HashAggregate is scan cost plus hashing plus a spill penalty. GroupAggregate without an index is scan cost plus a large sort. GroupAggregate with an index is scan cost plus a small streaming cost, making it the cheapest. estimated total cost → HashAggregate GroupAggregate (explicit Sort) GroupAggregate (ordered index) input access hashing / ordering spill penalty (estimated)

Annotated Evidence: The Same Query, Both Ways #

With no index on the grouping columns, the hash path wins comfortably:

EXPLAIN (ANALYZE) SELECT customer_id, count(*) FROM orders GROUP BY customer_id;

HashAggregate  (cost=189320.00..190520.00 rows=120000)  (actual time=1421.7..1502.3 rows=118422)
  Group Key: customer_id
  Batches: 1  Peak Memory Usage: 14385 kB       -- fits, so no spill penalty applies
  ->  Seq Scan on orders  (actual rows=8000000 loops=1)
Execution Time: 1544.8 ms

Force the alternative and the sort dominates the cost:

SET LOCAL enable_hashagg = off;

GroupAggregate  (cost=1043312.71..1103312.71 rows=120000)  (actual time=4102.9..5210.4 rows=118422)
  Group Key: customer_id
  ->  Sort  (actual time=4102.8..4790.1 rows=8000000 loops=1)
        Sort Method: external merge  Disk: 372160kB     -- the whole input, sorted on disk
Execution Time: 5261.7 ms

Now add the index and re-plan with hashing allowed again:

CREATE INDEX orders_customer_idx ON orders (customer_id);

GroupAggregate  (cost=0.43..288420.10 rows=120000)  (actual time=0.04..1180.2 rows=118422)
  Group Key: customer_id
  ->  Index Only Scan using orders_customer_idx on orders  (actual rows=8000000 loops=1)
        Heap Fetches: 0
Execution Time: 1204.6 ms

The planner switched back to GroupAggregate on its own. The sort term went to zero, the index-only scan reads fewer bytes than the heap scan, and the startup cost fell from 189,320 to 0.43 — which is what makes this shape so much better under a LIMIT.

Step-by-Step: Comparing Strategies Deliberately #

  1. Record the default plan with EXPLAIN (ANALYZE, BUFFERS) and note the aggregate node, Batches, and any Sort Method line.

  2. Force the other strategy inside a transaction so the setting cannot leak to the next user of a pooled connection:

    BEGIN;
SET LOCAL enable_hashagg = off;      -- see the sorted plan
EXPLAIN (ANALYZE, BUFFERS) SELECT customer_id, count(*) FROM orders GROUP BY customer_id;
ROLLBACK;

  3. Compare three numbers, not one: total execution time, temporary bytes (Disk: on the sort, Disk Usage: on the aggregate), and startup cost. A plan that is 10% slower but writes no temporary files is often the better production choice.

  4. Make the winner permanent through the data, not the switch. If the sorted path won, create the index that supplies the order — mind the column order rules in multicolumn index column ordering. If the hash path won but spilled, fix the group estimate or raise the allowance.

  5. Reset and verify. Confirm the default plan now matches the plan you chose, with no enable_* overrides in effect. EXPLAIN (SETTINGS) prints any that are still set.

Before and After #

-- BEFORE: no ordered path, hash table fits, blocking aggregate
HashAggregate  (startup cost=189320.00)  Batches: 1   Execution Time: 1544.8 ms
  -- with LIMIT 10 appended: still 1544.8 ms, the whole input must be consumed

-- AFTER: index supplies the order, streaming aggregate
GroupAggregate  (startup cost=0.43)                    Execution Time: 1204.6 ms
  -- with LIMIT 10 appended: 0.6 ms, the pipeline stops after ten groups

The unqualified query improved by 22%. The same query with a LIMIT improved by three orders of magnitude, because only the streaming shape can stop early.

Blocking versus streaming under a LIMIT The top pipeline shows a hash aggregate consuming all eight million rows before emitting anything, so a LIMIT saves nothing. The bottom pipeline shows an ordered index scan feeding a group aggregate that emits groups immediately, letting a LIMIT stop after ten groups. HashAggregate — blocking consume all 8,000,000 input rows emit 118,422 groups LIMIT 10 1545 ms GroupAggregate over an ordered index — streaming Index Only Scan emit group on key change LIMIT 10 stop signal reaches the scan after 10 groups 0.6 ms

Common Pitfalls #

Leaving enable_hashagg = off set after a test. A session-level SET on a pooled connection changes every subsequent plan on that backend. Diagnostic signal: EXPLAIN (SETTINGS) listing the GUC, or an unexplained fleet-wide plan change. Fix: always use SET LOCAL inside a transaction — see session parameter drift.

Comparing only total time. A hash plan that is marginally faster in isolation but writes 300 MB of temporary files will lose badly under concurrency. Diagnostic signal: Disk Usage or temp written in the buffers line. Fix: weigh temporary I/O alongside wall-clock.

Creating an index solely to force a strategy. The index must be maintained on every write and may bloat, as covered in index maintenance and bloat. Diagnostic signal: an index used by exactly one reporting query. Fix: confirm the aggregate is hot enough to justify the write cost.

Assuming the sorted path avoids memory pressure. It moves the pressure to the Sort node, which spills at work_mem — without the hash multiplier. Diagnostic signal: Sort Method: external merge Disk: under the aggregate. Fix: supply an ordered index rather than merely disabling hashing.

Signal to response for aggregate strategy mistakes Four rows pairing a diagnostic signal with the correct response: a leaked enable_hashagg setting, a plan judged on time alone, an index built only to force a strategy, and an external merge sort under a group aggregate. what you see what to do enable_hashagg listed in EXPLAIN (SETTINGS) scope it with SET LOCAL and re-plan plan faster but writes 300 MB of temp files weigh temporary I/O, not wall-clock alone index used by exactly one report confirm the write cost is worth it Sort Method: external merge under the aggregate supply the order with an index

Frequently Asked Questions #

Why did adding an index change my aggregate from hash to sorted? Because the ordering cost collapsed to nearly zero. GroupAggregate needs its input ordered on the grouping columns; once an index provides that, the sort leaves the comparison and the streaming path is cheaper. Dropping the index reverses the decision just as cleanly.

How do I force one strategy for testing? SET LOCAL enable_hashagg = off inside a transaction shows the sorted plan, and SET LOCAL enable_sort = off pushes toward hashing. Both are diagnostics for comparing real costs, not production settings — scope them to the transaction so they cannot leak through a pooler.

Does a LIMIT change which strategy wins? Often decisively. A hash aggregate must consume all of its input before emitting anything, while a sorted aggregate over an ordered path emits groups as it goes, so a small LIMIT can stop the pipeline almost immediately. With an ordered path available, the sorted plan can be thousands of times faster under a LIMIT.