The DISTINCT in your COUNT | boringSQL

Table of Contents

The schema

Two counts, two different plans

Why the planner can't split it

One DISTINCT poisons the whole statement

The rewrite: push the DISTINCT into a GROUP BY

ORDER BY aggregates hit the same wall

The harder case: per-group distinct counts

When to actually care

Here is a query that shows up in every analytics workload:

SELECT count(DISTINCT user_id) FROM events;<br>It looks like the cheapest possible thing: count the distinct users. On a machine with cores to spare you would 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.

CREATE TABLE events (<br>id bigint GENERATED ALWAYS AS IDENTITY,<br>user_id int NOT NULL,<br>country text NOT NULL,<br>amount numeric(10,2) NOT NULL<br>);

INSERT INTO events (user_id, country, amount)<br>SELECT (random()*50000)::int + 1,<br>(ARRAY['US','DE','GB','FR','JP','BR','IN','CA'])[(random()*7)::int + 1],<br>(random()*500)::numeric(10,2)<br>FROM generate_series(1, 10000000);

ANALYZE events;<br>max_parallel_workers_per_gather is at its default of 2 on fresh cluster. For these examples I raised it to 4 and work_mem to 64MB, so 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:

EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM events; Finalize Aggregate (actual rows=1.00 loops=1)<br>-> Gather (actual rows=5.00 loops=1)<br>Workers Planned: 4<br>Workers Launched: 4<br>-> Partial Aggregate (actual rows=1.00 loops=5)<br>-> Parallel Seq Scan on events (actual rows=2000000.00 loops=5)<br>Four workers plus the leader (loops=5) 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:

EXPLAIN (ANALYZE, COSTS OFF, BUFFERS) SELECT count(DISTINCT user_id) FROM events; Aggregate (actual rows=1.00 loops=1)<br>Buffers: shared hit=15915 read=47783, temp read=14681 written=14684<br>-> Sort (actual rows=10000000.00 loops=1)<br>Sort Key: user_id<br>Sort Method: external merge Disk: 117448kB<br>Buffers: shared hit=15915 read=47783, temp read=14681 written=14684<br>-> Seq Scan on events (actual rows=10000000.00 loops=1)<br>Buffers: shared hit=15912 read=47783<br>No Gather. No Partial Aggregate. No parallel scan. A single process reads all ten million rows, sorts every one of them by user_id so duplicates sit next to each other, then walks the sorted output counting the runs. The sort does not fit in 64MB of work_mem, so it spills 115MB to a temporary file on disk. 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, the thing that knows how to fold two partial states into one. count's combine function adds the partial counts. sum, avg, min, max all have one. This split, scan in parallel, combine at the end, is the entire basis of parallel query for aggregates.

count(DISTINCT user_id) has no usable combine step, and not because nobody wrote one. Think about what a worker could hand back. 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.

I checked this against PostgreSQL 17.10, 18.4, and 19beta1: partial aggregation still does not cover distinct and ordered aggregates on any of them.

debug_parallel_query is a way to check this isn't a cost estimate that happened to favor serial execution....