Postgres count(distinct) on 80M rows very slow, any trick?
Lin Wei@linwei
Daily active customers query take ~40s, dashboard timeout:
select date_trunc('day', created_at) as day,
count(distinct customer_id)
from orders
where created_at >= now() - interval '90 days'
group by 1
order by 1;Table ~80M rows, index on created_at. Postgres 15. EXPLAIN show big Sort spilling to disk. Tried increase work_mem to 256MB, bit faster but still 25s.
Is count(distinct) just slow in Postgres? Thinking to move this to warehouse but would like to understand first.
Accepted answer
count(distinct) in Postgres sorts the input for each group, it cannot use a hash aggregate. Rewrite so the distinct happens in a subquery, then the planner can use HashAggregate:
select day, count(*)
from (
select distinct date_trunc('day', created_at) as day, customer_id
from orders
where created_at >= now() - interval '90 days'
) t
group by day
order by day;
Also try an index on (created_at, customer_id) so it can be an index only scan. If still not enough, precompute daily distinct customers into a small table once a day, the past does not change.
Written by
Lin Wei
Data engineer at e-commerce company, Singapore. dbt, Airflow, Spark, too many dashboards. Trying to make the numbers match between finance and marketing.
Get the best of #select-star-anonymous weekly
One email each Monday with the most useful posts. No spam; unsubscribe in one click.
Comments
Sign in to join the conversation.
Sign in to commentAccepted answer
Arjun Mehta@arjunmehta
count(distinct) in Postgres sorts the input for each group, it cannot use a hash aggregate. Rewrite so the distinct happens in a subquery, then the planner can use HashAggregate:
select day, count(*)
from (
select distinct date_trunc('day', created_at) as day, customer_id
from orders
where created_at >= now() - interval '90 days'
) t
group by day
order by day;
Also try an index on (created_at, customer_id) so it can be an index only scan. If still not enough, precompute daily distinct customers into a small table once a day, the past does not change.
Lin Wei@linwei
rewrite alone go from 40s to 6s. with the new index 2.5s. thank you!! did not know distinct aggregate cannot hash
Tomasz Kowalski@tomasz_k
And if dashboard refresh every minute, precompute anyway. Database is not calculator for refresh button of marketing team.