GoKawiilHere is a query that shows up in every analytics workload:
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 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 ( id bigint GENERATED ALWAYS AS IDENTITY , user_id int NOT NULL , country text NOT NULL , amount numeric ( 10 , 2 ) NOT NULL ); INSERT INTO events (user_id, country, amount) SELECT (random() * 50000 ):: int + 1 , ( ARRAY ['US','DE','GB','FR','JP','BR','IN','CA'])[(random()*7)::int + 1], (random() * 500 ):: numeric ( 10 , 2 ) FROM generate_series ( 1 , 10000000 ); ANALYZE events;
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;
... continue reading