PostgreSQL中logdateID索引下distinct查询耗时过长问题咨询

核心原因 #

1. 顺序扫描慢的原因 #

执行select distinct logdateid from userlogs时，优化器选择顺序扫描+HashAggregate，耗时的核心点在于：

• 需全表扫描9亿行（对应525GB数据），即便RAID10 SSD的读写性能较强，读取如此大规模的数据集也需要大量时间；
• HashAggregate要对9亿条记录做哈希去重，内存无法容纳完整的哈希表，会触发磁盘溢出，进一步拉长整体耗时。

2. 索引仅扫描慢的原因 #

强制禁用顺序扫描后，Index Only Scan + Unique依然耗时很久，本质问题是：

• ix_userlogs_logdateid作为普通B-tree索引，内部存储了9亿条logdateid条目（每一行userlogs对应一个索引条目），哪怕只有104个唯一值，PostgreSQL仍需遍历整个索引的所有叶节点才能收集全所有唯一值；
• 如果表的可见性映射（VM）未完全更新，Index Only Scan会频繁回表检查行的可见性，额外增加IO开销；
• Unique操作需要遍历索引返回的全部9亿条记录，虽然索引有序可以快速去重，但遍历9亿条记录本身就会消耗大量CPU和IO资源。

最优解决方案 #

你已经维护了Logdates表存储所有唯一日志日期，且userlogs.logdateid是关联该表的外键——外键约束保证了userlogs中所有的logdateid都存在于Logdates表中，因此直接查询Logdates表即可瞬间得到结果：

SELECT logdateid FROM logdates;

备选优化方案（如果必须从userlogs查询） #

若因特殊场景限制必须从userlogs表获取唯一logdateid，可尝试以下操作：

1. 更新统计信息：让优化器准确掌握logdateid的基数，可能生成更优执行计划：

   ANALYZE userlogs;
   

2. 更新可见性映射：让Index Only Scan真正无需回表检查，减少IO开销：

   VACUUM userlogs;
   

3. 尝试分组查询：部分场景下GROUP BY的执行计划可能比DISTINCT更高效：

   SELECT logdateid FROM userlogs GROUP BY logdateid;
   

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