The common SQL query `SELECT count(DISTINCT user_id) FROM events;` appears simple but disables parallel query execution in Postgres. This is because the `DISTINCT` keyword within an aggregate function requires the database to see all values in one place, typically through a sort operation that cannot be parallelized effectively. Unlike a plain `count(*)`, which can be split across multiple workers with partial aggregates, `count(DISTINCT)` lacks a usable combine step for partial results.
When `count(DISTINCT)` is used, Postgres resorts to a serial plan, scanning the entire table on a single core and performing a sort that can spill to disk if it exceeds `work_mem`. This significantly impacts performance on large tables. Even with `debug_parallel_query` enabled, the planner struggles to introduce effective parallelism for distinct aggregates.
A workaround involves rewriting the query using a `GROUP BY` clause to find distinct `user_id`s first, and then counting the resulting groups. This `GROUP BY` operation *can* be parallelized, allowing Postgres to efficiently compute the distinct count across multiple workers.
Source: Hacker News · Summarized by HeadlinesBriefing