Counting unique users with count(DISTINCT user_id) is natural PostgreSQL SQL. On a large table, however, that aggregate does not become a parallel aggregate even when spare CPU cores are available.

The primary source examines this limitation through execution plans and a ten-million-row test. The practical question is not whether the SQL is valid, but how to recognize serial processing and temporary-file I/O when they become bottlenecks in an analytical workload.

PostgreSQL COUNT(DISTINCT) cannot use parallel aggregation

The primary source tested PostgreSQL 17.10, 18.4, and 19beta1 and found that count(DISTINCT user_id) did not use partial aggregation. As of August 6, 2026, the PostgreSQL versioning policy lists 18.4 as the current stable minor release, while PostgreSQL 19 Beta 2 is available.

This is not merely an observation from one benchmark. The PostgreSQL 18 parallel-plan documentation states that parallel aggregation is unsupported when an aggregate call contains DISTINCT or ORDER BY. The development documentation retains the same limitation.

That does not mean no part of any surrounding query can ever run in parallel. The confirmed limitation is that the aggregate node containing DISTINCT cannot be split into Partial Aggregate and Finalize Aggregate stages. Joins, subqueries, and planner cost decisions can still change the shape of the complete plan.

Partial aggregation depends on combining worker states

PostgreSQL parallel aggregation has two stages. Participating processes create partial results, Gather or Gather Merge transfers them, and the leader produces the final result. These stages appear as Partial Aggregate and Finalize Aggregate in a plan.

For count(*), each worker’s state is a number. Adding those partial counts produces the correct total, so the work is easy to divide.

A sum of per-worker distinct counts is not correct because the same user_id may occur in more than one worker’s input. Producing an exact global count requires reconciling the sets of values seen by the workers, not merely adding counts. The official documentation accordingly requires a parallel-safe aggregate and a combine function for parallel aggregation.

The primary source also demonstrates a consequence for neighboring aggregates. If sum(amount) and count(DISTINCT user_id) share one aggregate node, sum cannot use partial aggregation there even though it could do so by itself.

The ten-million-row test spilled a serial sort to disk

The primary source used a ten-million-row table with a NOT NULL user_id column and about 50,000 distinct values. It set max_parallel_workers_per_gather to 4 and work_mem to 64MB before comparing plans for count(*) and count(DISTINCT user_id).

The count(*) plan split a parallel sequential scan across four workers and the leader, then finalized their partial counts. The count(DISTINCT user_id) plan instead sorted all ten million rows in one process and used an external merge that spilled roughly 115MB to disk.

Across three runs in that environment, the reported medians were 1,211ms for the original query and 360ms for the GROUP BY rewrite described below. The roughly 3.4-times difference belongs to the primary source’s data, hardware, and settings; it is not a general performance promise. The official EXPLAIN guide notes that sampled statistics, platform differences, and table scale can change costs and selected plans.

On a small table, serial execution can already be fast enough, and a rewrite may add needless complexity. This becomes worth investigating when disk spills or uneven CPU use are actually visible on a large relation.

Moving DISTINCT into GROUP BY makes partial aggregation possible

For one global distinct count, the primary source moves deduplication into an inner GROUP BY and counts the resulting groups in the outer query.

SELECT count(*)
FROM (
  SELECT user_id
  FROM events
  GROUP BY user_id
) AS distinct_users;

Because the source table declares user_id as NOT NULL, this query and count(DISTINCT user_id) return the same result. If PostgreSQL splits the inner grouping into Partial HashAggregate and Finalize HashAggregate, each worker can reduce its input before sending groups to the leader.

A nullable column requires an extra guard. The PostgreSQL aggregate-expression specification says count(DISTINCT expression) counts distinct non-null values, while GROUP BY also produces one group for null. Filter nulls inside the subquery to preserve the original meaning.

SELECT count(*)
FROM (
  SELECT user_id
  FROM events
  WHERE user_id IS NOT NULL
  GROUP BY user_id
) AS distinct_users;

For distinct users per country, the same pattern can group by country, user_id inside and count by country outside. The primary source also found that this stacked grouping did not always receive a parallel plan; group cardinality and planner costs can still make a serial plan cheaper.

Compare the original and rewritten SQL with EXPLAIN ANALYZE

First verify that the original and rewritten statements return the same result for nulls, filters, duplicates introduced by joins, and each required grouping level. A faster query is unusable if it changes the metric.

Then collect plans for both statements with representative data volume and current statistics.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT count(DISTINCT user_id)
FROM events;

In the original plan, inspect a large Sort under Aggregate or GroupAggregate, any external merge and Disk report, and the presence or absence of Gather and Partial Aggregate. For the rewrite, check whether Partial HashAggregate, Gather, and Finalize HashAggregate appear, then compare hash batches, memory, and temporary-file use. These names are indicators rather than a guaranteed plan shape.

As the official documentation explains, EXPLAIN ANALYZE actually executes the statement and adds measurement overhead. Even a read-only query can add production load, so begin in a test environment or during a controlled window.

Parallel aggregation dates to PostgreSQL 9.6 but is not automatically faster

PostgreSQL 9.6, released on September 29, 2016, introduced initial parallel execution for large queries, including sequential scans, joins, and supported aggregates. It was never a promise that every aggregate would automatically run in parallel.

Current documentation likewise explains that final aggregation runs in the leader. When the number of groups is large relative to the input, the planner may judge parallel aggregation unhelpful and decline to select it. A GROUP BY rewrite therefore creates an opportunity for parallelism; it does not prove that the result will be parallel or faster.

Parallel execution also has a resource cost. The PostgreSQL resource settings documentation says limits such as work_mem apply to each worker and that a query with four workers can use up to roughly five times the CPU, memory, and I/O of a non-parallel query once the leader is included. Fewer workers than planned may also be available at execution time.

Increasing max_parallel_workers_per_gather or work_mem is therefore not a universal fix. Operational validation should include concurrency, total memory, temporary files, I/O, and actual worker availability—not only the latency of one isolated query.

Adopt the rewrite only after checking semantics and the real plan

count(DISTINCT ...) is valid and readable SQL. It can remain the better choice for small tables or infrequent aggregations. The limitation matters when a large fact table repeatedly shows a serial sort, disk spill, or observable resource imbalance.

In that case, build a GROUP BY alternative and compare result equivalence—including null behavior—representative execution plans, and total resource use after parallelization. The primary source’s 3.4-times result is evidence from one controlled test, not a target; the decision should come from the workload that will actually run in production.

Source

• Title: Radim Marek: The DISTINCT in your COUNT
• URL: https://postgr.es/p/9rw