PostgreSQL中count(distinct(username))查询过慢问题排查求助

1. 优化器选择全表扫描而非索引扫描 #

从执行计划可见，两次查询均采用Seq Scan（全表扫描），未使用你创建的idx_client_username索引。这是因为PostgreSQL优化器会评估IO成本：对于73万行的数据，全表扫描的连续IO开销被判定为低于索引扫描的随机IO开销；若表的统计信息过时，也会导致优化器做出错误的成本判断。

2. count(distinct)与group by + count(*)的性能差异 #

count(distinct)在执行时需要维护一个去重集合，内存占用和计算逻辑的开销远高于group by后再count(*)。从执行时间对比（1813ms vs 463ms）能明显看出，HashAggregate（group by的实现方式）的效率更优。

1. 优先采用高效查询写法 #

直接使用第二种查询方式，性能是原count(distinct)的3倍以上：

select count(*) as cnt from (select username from client group by username) t;

2. 更新表统计信息 #

过时的统计信息会干扰优化器的执行计划选择，执行以下命令更新：

ANALYZE client;

更新后重新执行查询，观察优化器是否会选择索引扫描。

3. 调整内存参数优化聚合性能 #

HashAggregate的性能依赖work_mem参数，内存不足时聚合操作会写入临时磁盘文件，拖慢速度。可临时调整会话级参数测试：

SET work_mem = '64MB'; -- 根据服务器内存情况调整，如16MB/32MB

若性能提升明显，可在postgresql.conf中永久修改该参数（需重启服务）。

4. 尝试强制索引扫描或优化索引 #

若更新统计信息后仍未使用索引，可临时关闭全表扫描测试索引性能：

SET enable_seqscan = off;
EXPLAIN ANALYZE select count(distinct(username)) as cnt from client;
SET enable_seqscan = on; -- 测试后恢复默认设置

如果索引扫描速度更优，说明优化器成本估算偏差，可降低random_page_cost参数（默认4，调整为2或3），让优化器更倾向于索引扫描。

5. 物化视图（适合频繁查询、非实时场景） #

若该查询需频繁执行且可接受数据延迟，可创建物化视图缓存结果：

CREATE MATERIALIZED VIEW mv_client_distinct_cnt AS
SELECT COUNT(DISTINCT username) AS cnt FROM client;

查询时直接访问物化视图：

SELECT cnt FROM mv_client_distinct_cnt;

定期刷新获取最新数据：

REFRESH MATERIALIZED VIEW mv_client_distinct_cnt;

内容的提问来源于stack exchange，提问作者Max Bugaenko