GroupAggregate in PostgreSQL EXPLAIN

What it does

GroupAggregate reads rows that arrive sorted by the GROUP BY columns. While the key stays the same it updates the running aggregates; when the key changes, it outputs the finished group and starts the next one. It only ever holds one group in memory.

Because groups come out one by one, in key order, the first group is available as soon as its last row has been read. That makes it a good fit under a LIMIT or an ORDER BY on the same key.

The sorted input comes from one of:

• an index on the group key (Index Scan or Index Only Scan),
• a Sort node directly below,
• another node that already outputs that order, such as a Merge Join or a Gather Merge (where you’ll see Finalize GroupAggregate).

When the planner picks it

• An index provides the group-key order cheaply, especially with an index-only scan.
• The query also wants ORDER BY the group key, so sorting once serves both.
• The planner expects too many groups for a hash table to fit in memory (HashAggregate would spill).
• Aggregates with ORDER BY or DISTINCT inside them (string_agg(x ORDER BY y)): since PostgreSQL 16 they can use input sorted for them in advance, which favours a Sort and a GroupAggregate.

Reading its numbers

GroupAggregate  (cost=0.42..359.88 rows=9200 width=12) (actual time=0.086..1.552 rows=500.00 loops=1)
  Group Key: customer_id
  Buffers: shared hit=13

• Group Key: the grouping columns or expressions.
• rows: groups output. Here the planner expected 9,200 groups and got 500. Group estimates are often rough: they’re based on each column’s number of distinct values, which is itself estimated.
• No memory or batch figures: it doesn’t need them. If the input comes from a Sort, look there for Sort Method and Disk.
• actual time: includes its input. The time spent aggregating is the difference from the child.

When it’s a problem

The aggregation is cheap. What’s under it often isn’t:

1. A full index scan that visits the table for every row. An ordered read of a whole table through an index can touch a different table page for each row. In the example below, grouping all 1,000,000 orders by customer this way took 1,000,000 buffer accesses and 828 ms. Fixes:
   • A covering index so the scan becomes an index-only scan: CREATE INDEX ON orders (customer_id) INCLUDE (total). Here: 236 ms, 3,835 pages. The trade-off is a bigger index to maintain on every write.
   • Or let the planner hash instead: compare with SET enable_indexscan = off in your session (as a test). Here the parallel HashAggregate plan took 363 ms.
2. A big Sort beneath it that spills. Sort Method: external merge under a GroupAggregate means the sort didn’t fit in work_mem. Raise work_mem for the query, or provide the order with an index.
3. Group count estimates far off. They drive the choice between hashing and sorting and the plan above. For expressions (date_trunc('day', created_at)) the planner has no statistics and guesses; for several related columns, CREATE STATISTICS … (ndistinct) on them helps.

Example

PostgreSQL 18.6, default settings:

CREATE SCHEMA seo_explain;
SET search_path = seo_explain;

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id int NOT NULL,
  status      text NOT NULL,
  total       numeric(10,2) NOT NULL,
  created_at  timestamptz NOT NULL
);
INSERT INTO orders
SELECT i,
       1 + (i::bigint * 7919) % 50000,
       CASE WHEN i % 100 < 90 THEN 'shipped'
            WHEN i % 100 < 97 THEN 'pending'
            ELSE 'refunded' END,
       round(((i * 37) % 100000) / 100.0, 2),
       timestamptz '2024-01-01' + i * interval '1 minute'
FROM generate_series(1, 1000000) AS i;
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_created_at_idx ON orders (created_at);
VACUUM ANALYZE orders;

(Our test table also had a foreign key to customers; it doesn’t change these plans.)

Order counts for the first 500 customers, straight from the index:

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, count(*) FROM orders WHERE customer_id <= 500 GROUP BY customer_id;

GroupAggregate  (cost=0.42..359.88 rows=9200 width=12) (actual time=0.086..1.552 rows=500.00 loops=1)
  Group Key: customer_id
  Buffers: shared hit=13
  ->  Index Only Scan using orders_customer_id_idx on orders  (cost=0.42..217.33 rows=10109 width=4) (actual time=0.080..0.746 rows=10000.00 loops=1)
        Index Cond: (customer_id <= 500)
        Heap Fetches: 0
        Index Searches: 1
        Buffers: shared hit=13
Planning:
  Buffers: shared hit=130
Planning Time: 0.589 ms
Execution Time: 1.611 ms

Orders per day for a week, with a Sort providing the order:

EXPLAIN (ANALYZE, BUFFERS)
SELECT date_trunc('day', created_at) AS day, count(*)
FROM orders
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-08'
GROUP BY 1 ORDER BY 1;

GroupAggregate  (cost=922.77..1107.95 rows=9259 width=16) (actual time=2.515..3.428 rows=7.00 loops=1)
  Group Key: (date_trunc('day'::text, created_at))
  Buffers: shared hit=6 read=28
  ->  Sort  (cost=922.77..945.91 rows=9259 width=8) (actual time=2.347..2.693 rows=10080.00 loops=1)
        Sort Key: (date_trunc('day'::text, created_at))
        Sort Method: quicksort  Memory: 385kB
        Buffers: shared hit=6 read=28
        ->  Index Only Scan using orders_created_at_idx on orders  (cost=0.42..312.75 rows=9259 width=8) (actual time=0.085..1.886 rows=10080.00 loops=1)
              Index Cond: ((created_at >= '2024-03-01 00:00:00+00'::timestamp with time zone) AND (created_at < '2024-03-08 00:00:00+00'::timestamp with time zone))
              Heap Fetches: 0
              Index Searches: 1
              Buffers: shared hit=3 read=28
Planning:
  Buffers: shared hit=11
Planning Time: 0.090 ms
Execution Time: 3.459 ms

The planner expected 9,259 days in a week (one group per row, because it knows nothing about date_trunc’s output) and got 7. Harmless here; in a bigger query, an estimate that far off could change the join strategy above it.

When the input is the problem

All customers’ order counts and totals:

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

GroupAggregate  (cost=0.42..60605.68 rows=51088 width=44) (actual time=0.368..826.139 rows=50000.00 loops=1)
  Group Key: customer_id
  Buffers: shared hit=995833 read=5113
  ->  Index Scan using orders_customer_id_idx on orders  (cost=0.42..52467.08 rows=1000000 width=10) (actual time=0.088..718.361 rows=1000000.00 loops=1)
        Index Searches: 1
        Buffers: shared hit=995833 read=5113
Planning:
  Buffers: shared hit=30
Planning Time: 0.121 ms
Execution Time: 827.950 ms

The planner expected this to be cheap: the 65 MB table fits easily within effective_cache_size, so it assumed repeat visits to the same pages would come from cache. They did, but walking a million rows in customer_id order still meant a separate page access for nearly every row, and that took 718 ms. With a covering index:

CREATE INDEX orders_customer_id_total_idx ON orders (customer_id) INCLUDE (total);
VACUUM ANALYZE orders;

GroupAggregate  (cost=0.42..38531.99 rows=49565 width=44) (actual time=0.045..233.284 rows=50000.00 loops=1)
  Group Key: customer_id
  Buffers: shared hit=1 read=3834 written=38
  ->  Index Only Scan using orders_customer_id_total_idx on orders  (cost=0.42..30412.42 rows=1000000 width=10) (actual time=0.035..121.655 rows=1000000.00 loops=1)
        Heap Fetches: 0
        Index Searches: 1
        Buffers: shared hit=1 read=3834 written=38
Planning:
  Buffers: shared hit=11
Planning Time: 0.086 ms
Execution Time: 235.527 ms

The same million rows, read from 3,835 index pages instead of a million table-page visits.

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts, so the index scan doing the real work under a GroupAggregate is the node that stands out.