Aggregate in PostgreSQL EXPLAIN

What it does

Aggregate reads every row from its child and feeds it to the aggregate functions in the query: count(*), sum(total), avg(total), max(created_at), string_agg(…), count(*) FILTER (WHERE …). When the input ends, it returns one row with the results.

In text plans, the label tells you the strategy:

In a parallel plan each of these may appear twice. Partial Aggregate runs in every process and produces a partial state (a count, a running sum and count for avg); Finalize Aggregate, above the Gather, combines the partial states into the final answer.

When the planner picks it

Whenever the query has aggregate functions and no GROUP BY. The interesting choice is underneath: how to read the rows. Two things change that:

• Parallelism. On a large table, Finalize Aggregate → Gather → Partial Aggregate → Parallel Seq Scan lets two or more processes count at once. Aggregates with DISTINCT or ORDER BY inside them can’t be split this way.
• min() and max() on an indexed column don’t use Aggregate at all. PostgreSQL rewrites SELECT max(created_at) FROM orders into “read one entry from the end of the index”: a Result with an InitPlan → Limit → Index Only Scan Backward. It took 0.3 ms on a million rows.

Reading its numbers

The node has few fields of its own:

Aggregate  (cost=81.99..82.00 rows=1 width=40) (actual time=0.063..0.064 rows=1.00 loops=1)
  Buffers: shared hit=23

• rows=1: always one row for a plain aggregate.
• actual time: includes the time of everything below it. The aggregate’s own work is the difference between its time and its child’s: here 0.064 − 0.052 = 0.012 ms. Usually the scan beneath is what costs.
• cost=81.99..82.00: startup cost equals almost the whole cost, because nothing comes out until all input is read.
• Partial Aggregate … rows=1.00 loops=3 in a parallel plan: one partial result per process. The Gather above shows rows=3.00: three partial states going to Finalize Aggregate.
• Output: PARTIAL count(*) (with VERBOSE): shows which values are partial states.

When it’s a problem

Slow aggregates are almost always slow scans. The most common case is counting a large table.

• SELECT count(*) FROM big_table reads the whole table. PostgreSQL doesn’t keep a row count, because different transactions can see different numbers of rows. Options:
  • If an estimate will do (a dashboard, a pagination label), read SELECT reltuples::bigint FROM pg_class WHERE oid = 'orders'::regclass;, which is updated by VACUUM and ANALYZE. Instant, approximately right.
  • If it must be exact and frequent, keep a counter table updated by triggers. Trade-off: every insert and delete then updates one row, which can become a point of contention.
  • An Index Only Scan on a small index can be cheaper than reading the table, provided the table is well vacuumed (Heap Fetches near 0).
• Counting with a condition reads whatever the condition needs; an index on the filtered column turns a full scan into a small one, as in the example.
• count(DISTINCT x) sorts or hashes inside the aggregate and can’t be parallelised. Counting over SELECT DISTINCT x in a subquery lets the planner use a HashAggregate or a parallel plan for the distinct step.
• Workers didn’t launch. If Workers Launched is below Workers Planned, the leader counted alone. See Gather.

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.)

One customer’s order count and average:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*), avg(total) FROM orders WHERE customer_id = 4242;

Aggregate  (cost=81.99..82.00 rows=1 width=40) (actual time=0.063..0.064 rows=1.00 loops=1)
  Buffers: shared hit=23
  ->  Bitmap Heap Scan on orders  (cost=4.58..81.89 rows=20 width=6) (actual time=0.029..0.052 rows=20.00 loops=1)
        Recheck Cond: (customer_id = 4242)
        Heap Blocks: exact=20
        Buffers: shared hit=23
        ->  Bitmap Index Scan on orders_customer_id_idx  (cost=0.00..4.58 rows=20 width=0) (actual time=0.018..0.018 rows=20.00 loops=1)
              Index Cond: (customer_id = 4242)
              Index Searches: 1
              Buffers: shared hit=3
Planning Time: 0.078 ms
Execution Time: 0.087 ms

Counting the refunded orders in the whole table, in parallel:

EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM orders WHERE status = 'refunded';

Finalize Aggregate  (cost=14572.31..14572.32 rows=1 width=8) (actual time=40.765..42.755 rows=1.00 loops=1)
  Buffers: shared hit=6740 read=1594 written=129
  ->  Gather  (cost=14572.09..14572.30 rows=2 width=8) (actual time=38.761..42.748 rows=3.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=6740 read=1594 written=129
        ->  Partial Aggregate  (cost=13572.09..13572.10 rows=1 width=8) (actual time=33.416..33.417 rows=1.00 loops=3)
              Buffers: shared hit=6740 read=1594 written=129
              ->  Parallel Seq Scan on orders  (cost=0.00..13542.33 rows=11903 width=0) (actual time=0.202..32.861 rows=10000.00 loops=3)
                    Filter: (status = 'refunded'::text)
                    Rows Removed by Filter: 323333
                    Buffers: shared hit=6740 read=1594 written=129
Planning Time: 0.070 ms
Execution Time: 42.790 ms

Each process counted about 10,000 rows; the leader added three partial counts. The aggregates themselves took well under a millisecond; the scan took the rest.

The newest order, which needs no Aggregate node:

EXPLAIN (ANALYZE, BUFFERS) SELECT max(created_at) FROM orders;

Result  (cost=0.45..0.46 rows=1 width=8) (actual time=0.205..0.208 rows=1.00 loops=1)
  Buffers: shared hit=2 read=3
  InitPlan 1
    ->  Limit  (cost=0.42..0.45 rows=1 width=8) (actual time=0.203..0.203 rows=1.00 loops=1)
          Buffers: shared hit=2 read=3
          ->  Index Only Scan Backward using orders_created_at_idx on orders  (cost=0.42..26088.42 rows=1000000 width=8) (actual time=0.202..0.202 rows=1.00 loops=1)
                Heap Fetches: 0
                Index Searches: 1
                Buffers: shared hit=2 read=3
Planning:
  Buffers: shared hit=129
Planning Time: 1.576 ms
Execution Time: 0.300 ms

In Inlet

Inlet draws EXPLAIN ANALYZE as a tree and highlights the slowest step and badly misestimated row counts. When you browse a table, it pages through rows with an estimated or exact count, so you can settle for the cheap estimate when that’s enough.