PostgreSQL统计distinct列值慢：索引未生效的性能优化求助

首先，咱们先搞明白为啥你建的group_id单列索引没帮上count(distinct group_id)的忙——PostgreSQL的查询优化器可不是盲目用索引的，它会计算整体成本：对于300万行的表，全表扫描的连续磁盘IO反而可能比索引扫描的随机IO更高效，尤其是当你只需要group_id这一列时，单列索引的大小和表的大小差不了多少，优化器自然会选成本更低的全表扫描。而且不管用不用索引，count(distinct)都得做去重的聚合操作，这一步才是耗时的核心。

对你测试结果的分析 #

咱们逐个看你做的测试：

• 关闭顺序扫描后更慢：强制用索引扫描的话，虽然能拿到group_id，但索引的随机IO开销比全表的连续IO大太多，300万行下来自然更慢。
• 添加group_id > 380后反而变慢：这个条件过滤后的数据量估计还是不小，优化器可能选了索引扫描，但随机IO+聚合的成本反而超过了全表扫描；也可能是统计信息不准确，导致它选错了执行计划。
• 调大work_mem到10MB变快：这才是关键！默认4MB的work_mem太小了，300万整数型的group_id去重需要的内存远超4MB，之前是把去重的中间结果写到磁盘临时表，慢得要死；调大后能在内存里完成哈希聚合，速度自然上来了。
• 改写SQL性能提升：group by和distinct的执行计划略有不同，PostgreSQL对group by的哈希聚合优化可能更到位，所以能快一点，但本质还是依赖足够的work_mem。
• 创建视图后快到飞起：如果是普通视图，那大概率是优化器生成了更高效的执行计划，但我猜你实际用的是物化视图？普通视图只是SQL语法糖，执行时还是会扫原表。物化视图是把distinct group_id的结果预存在小表里（只有481行），查的时候直接读小表当然快，但隐患也很明显：原表数据更新后，物化视图不会自动同步，得手动刷新，这会影响数据的时效性。

针对性优化方案 #

根据你的业务需求，分两种情况给出最优方案：

1. 要求数据实时更新 #

• 调大work_mem到合适值：按你的数据量，把work_mem调到16-20MB应该就能让哈希聚合完全在内存里完成。先做会话级测试：

  set work_mem = '20MB';
  select count(distinct group_id) from everything_crowberry;
  

  如果效果稳定，再在postgresql.conf里全局修改（需要重启生效），或者针对特定用户/表设置。
• 改写SQL为分组查询：用以下写法代替count(distinct)，在很多场景下执行计划更高效：

  select count(*) from (select group_id from everything_crowberry group by group_id) t;
  

• 更新统计信息：执行ANALYZE everything_crowberry;，让优化器拿到最新的表数据分布，做出更合理的执行计划选择。

2. 可以接受数据有一定延迟 #

• 创建物化视图：这是最快的方案，直接预存去重后的结果：

  CREATE MATERIALIZED VIEW mv_distinct_groups AS
  SELECT DISTINCT group_id FROM everything_crowberry;
  

  查询的时候直接查物化视图：

  select count(*) from mv_distinct_groups;
  

  记得定期刷新数据，比如用REFRESH MATERIALIZED VIEW mv_distinct_groups;。如果需要自动刷新，可以用pg_cron定时任务，或者在原表的增删改触发器里触发刷新（但触发器会增加写操作的开销）。

另外，你的表已经有包含group_id的唯一约束，这个约束本身就是个多列索引，但对于count(distinct group_id)来说，单列的group_id索引更轻便，但优化器还是可能选全表扫描，所以核心还是解决聚合时的内存问题和数据预计算的问题。

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