postgres sql distinct

• https://boringsql.com/posts/distinct-in-your-count/

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. Scan in parallel, combine at the end. That's the whole logic behind parallel aggregation.

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. A hash table of groups can be merged across workers.

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 falls back to serial.

Revrite distinct

SELECT count(distinct user_id) FROM events;

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

SELECT country, count(DISTINCT user_id) FROM events GROUP BY country;

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