PostgreSQL在执行包含DISTINCT关键字的聚合函数时存在性能瓶颈。当查询中使用COUNT(DISTINCT user_id)这类操作时，查询优化器会禁用整个查询的并行处理机制，导致系统采用单核处理方式遍历整个表[1]。这一限制不仅影响该聚合函数本身，还会禁用并行查询的其他部分[1]。
根据分析，PostgreSQL之所以禁用并行处理是因为DISTINCT聚合操作无法有效实现分布式部分聚合的合并机制[1]。为了突破这一瓶颈，可以采用将DISTINCT转化为GROUP BY子查询的方式进行重写[1]。具体来说，将原始查询改写为SELECT count(*) FROM (SELECT user_id FROM events GROUP BY user_id) s的形式[1]，这样能够使查询恢复并行处理能力。根据测试结果，经过优化后的查询性能提升约3.4倍，且性能优势随着表行数增加而进一步扩大[1]。
相同的并行处理限制也适用于ORDER BY聚合以及有序集聚合函数（如string_agg和array_agg）等场景[1]。需要注意的是，性能影响主要出现在处理数百万行以上的大表时比较明显，而对于较小规模的表则无明显差异[1]。
Using COUNT(DISTINCT) in PostgreSQL prevents the query optimizer from enabling parallel query execution, a constraint that impacts not just the aggregate function itself but the entire query [1]. This limitation stems from the database's inability to effectively implement distributed partial aggregation merging for DISTINCT operations [1].
To work around this performance bottleneck, developers can restructure the query by converting DISTINCT into a GROUP BY subquery: SELECT count(*) FROM (SELECT user_id FROM events GROUP BY user_id) s [1]. This rewritten approach enables parallel processing and delivers approximately a 3.4-fold performance improvement [1]. The performance gap widens significantly as table sizes increase, making the optimization particularly valuable for large datasets containing millions of rows or more [1]. On smaller tables, the performance difference remains negligible [1].
The same restriction applies to other ordered aggregates and aggregate functions that depend on ordering, including string_agg and array_agg [1].