COUNT(DISTINCT) Plan Costs and Rewrites #

SELECT count(DISTINCT user_id) FROM page_views WHERE day >= '2026-09-01' takes 48 seconds. The plan is a single Aggregate over a Seq Scan, with no Sort node, no HashAggregate, and no parallel workers — and yet SELECT count(*) FROM (SELECT DISTINCT user_id …) s over the same rows finishes in 9 seconds with four workers. The two queries return the same number. They are planned completely differently.

Aggregation strategies in general are covered in aggregate and grouping strategies. DISTINCT inside an aggregate is a special case with its own limitations.

The Planner Condition #

An aggregate with DISTINCT or ORDER BY inside its argument list — count(DISTINCT x), string_agg(x, ',' ORDER BY x), array_agg(DISTINCT x) — needs its input values deduplicated or ordered per group. Historically the executor did this inside the Aggregate node itself: it collected each group’s values, sorted them internally, and skipped duplicates. Consequences:

PostgreSQL 16 added presorted aggregates: when it is cheaper, the planner places an explicit Sort below the Aggregate so the input already arrives in DISTINCT/ORDER BY order, making the sort visible and allowing an index to supply the order. This is controlled by enable_presorted_aggregate.

The rewrite count(*) FROM (SELECT DISTINCT x …) turns the problem into an ordinary DISTINCT, which the planner can implement as a HashAggregate, a sort-based Unique, or a parallel partial aggregate — the full set of strategies described in DISTINCT vs GROUP BY plan differences.

Same answer, different planner options Left, count DISTINCT user_id: the distinct step runs inside the Aggregate node as a sort, no hash strategy is possible, and no partial aggregation across workers. Right, count star over a SELECT DISTINCT subquery: the planner may choose HashAggregate or Unique, can aggregate in parallel workers, and shows its memory and spill figures. count(DISTINCT user_id) distinct done inside Aggregate always sort-based no Partial / Finalize split spill hidden before PostgreSQL 16 count(*) FROM (SELECT DISTINCT …) HashAggregate or Sort + Unique planner picks the cheaper strategy parallel partial aggregation possible Memory and Disk Usage reported the rewrite is only equivalent for a single DISTINCT aggregate

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(DISTINCT user_id) FROM page_views WHERE day >= '2026-09-01';

PostgreSQL 15:

Aggregate  (actual time=48102.6..48102.6 rows=1 loops=1)
  Buffers: shared hit=412008 read=640220, temp read=184220 written=184220
  ->  Seq Scan on page_views  (actual time=0.02..6104.2 rows=84020110 loops=1)
        Filter: (day >= '2026-09-01'::date)
Execution Time: 48210.4 ms
-- 42 s of the runtime is inside Aggregate: the hidden per-group sort
-- temp written=184220 blocks (~1.4 GB) belongs to that invisible sort
-- no Gather: DISTINCT aggregates cannot be split across workers

The rewrite:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM (
  SELECT DISTINCT user_id FROM page_views WHERE day >= '2026-09-01'
) s;
Aggregate  (actual time=9204.8..9204.8 rows=1 loops=1)
  ->  HashAggregate  (actual time=8904.2..9110.6 rows=3104220 loops=1)
        Group Key: page_views.user_id
        Planned Partitions: 4  Batches: 5  Memory Usage: 262193kB  Disk Usage: 204800kB
        ->  Gather  (actual time=0.9..4210.2 rows=84020110 loops=1)
              Workers Planned: 4  Workers Launched: 4
              ->  Parallel Seq Scan on page_views  (actual rows=16804022 loops=5)
                    Filter: (day >= '2026-09-01'::date)
Execution Time: 9310.2 ms
-- hashing 3.1M distinct keys beat sorting 84M values; scan ran in parallel
Where the time went in count(DISTINCT) Three fields are annotated. Aggregate actual time far above the scan's time shows work inside the aggregate. Temp written blocks on the Aggregate show a hidden sort spilled. The absence of Gather and Partial Aggregate shows the aggregation could not be parallelised. Aggregate (actual time=48102.6) over scan 6104.2 42 s spent inside the aggregate temp read=184220 written=184220 the invisible sort spilled (no Gather / Partial Aggregate) DISTINCT aggregates run serially time inside Aggregate minus child time = cost of the distinct step

Step-by-Step Resolution #

  1. Measure the gap between the Aggregate node’s actual time and its child’s. That difference is the distinct processing.

  2. Rewrite single count(DISTINCT) queries into a DISTINCT subquery and compare plans, as above.

  3. For grouped queries, rewrite with two levels of grouping:

    -- original
SELECT day, count(DISTINCT user_id) FROM page_views GROUP BY day;
-- rewrite
SELECT day, count(*) FROM (
  SELECT DISTINCT day, user_id FROM page_views
) s GROUP BY day;

  4. On PostgreSQL 16+, check whether an index can feed a presorted aggregate. An index on (day, user_id) lets the planner deliver input already ordered for GROUP BY day and the per-group distinct, removing the sort entirely.

    CREATE INDEX CONCURRENTLY page_views_day_user_idx ON page_views (day, user_id);

  5. Consider approximate counts when exactness is unnecessary: maintaining daily distinct counts in a summary table, or an approximation extension, trades precision for predictable cost. Precomputation options are covered in materialized view strategy.

  6. Size memory for the chosen strategy. A HashAggregate over millions of distinct keys uses the hash memory budget; check Batches and Disk Usage and follow HashAggregate spill diagnosis if it spills heavily.

Four ways to count 3.1 million distinct users count DISTINCT on PostgreSQL 15 takes 48.2 seconds. The DISTINCT subquery rewrite with a parallel scan and HashAggregate takes 9.3 seconds. With an index on day and user_id on PostgreSQL 16 the presorted plan takes 6.1 seconds. A precomputed daily summary table answers in 2 milliseconds. count(DISTINCT), PG 15 48.2 s DISTINCT subquery 9.3 s presorted via index, PG 16 6.1 s summary table 2 ms exact answers on raw rows still read all 84 million values

Before and After #

-- BEFORE: count(DISTINCT user_id)
Aggregate (actual time=48102.6)  temp written=184220  → Seq Scan (no workers)       Execution Time: 48210.4 ms

-- AFTER: count(*) over SELECT DISTINCT
Aggregate → HashAggregate (Batches: 5) → Gather (4 workers) → Parallel Seq Scan      Execution Time: 9310.2 ms

When the rewrite is not equivalent #

The subquery rewrite is exact for one DISTINCT aggregate. Queries with several — count(DISTINCT user_id), count(DISTINCT session_id) in the same SELECT — cannot be collapsed into one DISTINCT subquery, because each aggregate deduplicates a different column. Rewrite them as separate subqueries joined on the grouping key, or as GROUPING SETS over each column followed by conditional counting; each branch then gets its own hash or sort strategy. Also keep null semantics in mind: count(DISTINCT x) ignores nulls, while SELECT DISTINCT x returns one null row that count(*) would count. Use count(x) in the outer query, or filter WHERE x IS NOT NULL inside, to keep the results identical.

Aggregates with ORDER BY arguments, such as string_agg(name, ', ' ORDER BY name), have the same serial, hidden-sort behaviour. Their rewrite is to order in a subquery — but ordering of a subquery’s output is not guaranteed to survive into the aggregate unless it is the only input, so prefer the in-aggregate ORDER BY and give it an index to presort from on PostgreSQL 16 and later.

Common Pitfalls #

Looking for a Sort node that is not there. Before PostgreSQL 16 the distinct sort is internal. Diagnostic signal: aggregate time far above its child with temp I/O. Fix: attribute the gap to the distinct step.

Expecting workers to help count(DISTINCT). It cannot be partially aggregated. Diagnostic signal: no Partial Aggregate despite a large parallel-eligible scan. Fix: the subquery rewrite.

Counting nulls after rewriting. SELECT DISTINCT keeps one null. Diagnostic signal: result one higher than before. Fix: count(user_id) in the outer query.

Raising work_mem for a hidden sort globally. Every sort benefits and consumes. Diagnostic signal: memory growth across workloads. Fix: scope the setting to the reporting transaction.

Frequently Asked Questions #

Why is count(DISTINCT) so slow in PostgreSQL? #

The distinct step runs inside the Aggregate node as a sort of all input values per group, cannot use a hash table, and cannot be parallelised across workers. On large inputs that internal sort often spills to disk without appearing as a separate plan node.

Is SELECT count(*) FROM (SELECT DISTINCT x) faster than count(DISTINCT x)? #

Frequently, because the subquery’s DISTINCT can use a HashAggregate and parallel partial aggregation, while count(DISTINCT) is limited to a serial internal sort. Make sure null handling matches when you rewrite.

What changed for DISTINCT aggregates in PostgreSQL 16? #

The planner can add an explicit Sort below the Aggregate, or use an index, so the input is presorted for the aggregate’s DISTINCT or ORDER BY. The sort becomes visible in EXPLAIN and can be avoided entirely with a matching index.

Up: Aggregate and Grouping Strategies