Postgres COUNT(DISTINCT) silently kills parallel query — here's the fix

Every analytics workload has this query: SELECT count(DISTINCT user_id) FROM events;. It looks like the cheapest possible thing — count the distinct users. On a machine with cores to spare you'd expect Postgres to throw a few parallel workers at it, the way it does for almost any large scan. It does not. That one keyword, DISTINCT, switches off parallel query for the entire statement, and the larger your table the more it costs you. No setting or index changes that; the reason is in how the aggregate has to execute.

The schema

Ten million events, about fifty thousand distinct users, a handful of countries. Nothing unusual. With max_parallel_workers_per_gather raised to 4 and work_mem to 64MB, there's no resource starvation to blame for the plans below.

Two counts, two different plans

Start with a plain count(*), which has nothing to deduplicate. The plan shows a Finalize Aggregate over a Gather with four workers launched, each running a Partial Aggregate over a Parallel Seq Scan. Four workers plus the leader each scan their slice and keep a running count, and the leader adds the five partial counts together at the end.

Now add one word: count(DISTINCT user_id). The plan collapses to a serial Aggregate over a Sort of all ten million rows by user_id, spilling 115MB to disk. No Gather, no Partial Aggregate, no parallel scan. One core, the whole table, plus disk IO that the parallel count(*) never touched.

Why the planner can't split it

The sort is how Postgres computes DISTINCT inside an aggregate: order the values and adjacent equal ones collapse. A hash table is the other option, but the classic DISTINCT-aggregate path sorts. Either way it has to see every value in one place, which is the whole problem.

Parallel aggregation in Postgres works in two halves. Each worker runs a Partial Aggregate that builds transition state, a small running summary of the rows it has seen. For count that state is just a number. The leader then runs a Finalize Aggregate that merges those partial states with the aggregate's combine function. count's combine function adds the partial counts. sum, avg, min, max all have one.

count(DISTINCT user_id) has no usable combine step. To merge two workers' results into a correct global distinct count, the leader would need to know which users each worker saw, because a user that appears in worker 1's slice and again in worker 2's slice must be counted once, not twice. A partial count of distinct values cannot be combined; you would have to ship the entire set of distinct values from every worker and union them. At that point you have moved all the data to one place anyway, which is exactly what parallel aggregation exists to avoid.

An aggregate carrying DISTINCT (or an inner ORDER BY) therefore cannot run in partial mode, the planner cannot place a Partial Aggregate under a Gather, and with no partial aggregate to feed, a parallel scan buys nothing. The whole plan collapses to serial. This holds across PostgreSQL 17.10, 18.4, and 19beta1 — partial aggregation still does not cover distinct and ordered aggregates on any of them.

debug_parallel_query confirms this isn't a cost estimate that happened to favor serial execution. Set to on, it forces a parallel plan wherever one is legal. The result shows a Gather with Workers Planned: 1 and Single Copy: true: one process runs the entire plan, sort included, and the Gather node only exists to route its output back through the executor's parallel machinery. Nothing about the aggregate, the sort, or the scan actually splits across workers.

Notably, FILTER does not have this problem. count(*) FILTER (WHERE country='US') gets the same parallel shape as plain count(*), because FILTER just decides which rows each worker folds into its partial count.

One DISTINCT poisons the whole statement

The cost is not scoped to the distinct aggregate. It is scoped to the aggregation node it shares a query block with. Put a perfectly parallelizable aggregate next to a distinct one in the same SELECT and both lose parallelism, because one Aggregate node computes both and it can only run one way. An aggregate in a separate subquery or CTE is a different node and isn't affected.

For example, SELECT sum(amount), count(DISTINCT user_id) FROM events; — sum(amount) on its own would have run across four workers. Sharing a SELECT with one count(DISTINCT) drags it down to the same serial sort.

The rewrite: push the DISTINCT into a GROUP BY

Do the deduplication with the one operation Postgres can parallelize, a GROUP BY, and count the groups afterward:

SELECT count(*) FROM (SELECT user_id FROM events GROUP BY user_id) s;

This lets each worker deduplicate its own slice in parallel, then the leader merges the partial group sets. The tradeoff is that the planner can't always prove the rewrite is equivalent — especially when the distinct column is nullable or when you need per-group distinct counts — so it may not choose this plan automatically.

The harder case is per-group distinct counts, like SELECT country, count(DISTINCT user_id) FROM events GROUP BY country;. Here the DISTINCT is inside a group, and the same serial sort applies within each group. The rewrite is trickier: you'd need to deduplicate (user_id, country) pairs first, then group by country. That's a two-step query that the planner won't derive for you.

When to actually care

If your table fits in memory and your query runs in milliseconds, none of this matters. But for large analytics tables, a count(DISTINCT) that spills to disk can be an order of magnitude slower than the parallel alternative. Before you reach for a COUNT(DISTINCT), check the plan. If you see a serial Sort on a big table, consider the GROUP BY rewrite — or move the distinct aggregate into its own subquery so it doesn't poison the parallelism of the rest of your query.