PostgreSQL大表慢关联查询：原因解析与优化方案

问题描述 #

我有三张表：

• uploads：持续增长的表，当前约1000万行
• germanypostcodelookups：固定数据的表，共8307行
• postcodelookups：固定数据的表，共200万行

执行uploads与germanypostcodelookups的关联聚合查询时速度极慢（耗时约187秒），执行计划显示通过uploads_postal索引扫描后过滤了9825393行数据；但执行uploads与postcodelookups的同类查询却非常快（耗时约83毫秒），该查询采用先通过uploads_id索引定位数据再关联的方式。

尽管前者需要聚合的uploads行数更少，为何两者性能差异如此巨大？如何优化这个慢查询？

性能差异的核心原因 #

1. 执行计划选择错误
   数据库优化器选错了执行路径：对慢查询采用了「先扫大表（uploads）再关联小表」的逻辑，通过uploads_postal索引扫描后仍要过滤近千万行数据；而快查询采用了更高效的「小表驱动大表」模式，先处理postcodelookups，再用uploads_id索引快速定位匹配行，避免了全量扫描大表。

2. 统计信息过时
   uploads表持续增长，数据库的统计信息可能没有及时更新，导致优化器错误估算了关联后的行数，误以为uploads_postal索引能快速过滤出少量数据，最终选择了低效路径。

3. 索引适用性不足
   uploads_postal可能是单列索引，或postal字段重复率极高、选择性差，导致扫描索引后仍需回表过滤大量数据；而uploads_id是主键/唯一索引，选择性100%，能直接定位目标行。

优化方案 #

1. 强制指定小表驱动大表的执行计划

用STRAIGHT_JOIN（以MySQL为例）强制数据库先扫描小表germanypostcodelookups，再用其postal值匹配uploads的索引，避免先扫大表：

SELECT gp.postal, COUNT(u.id)
FROM germanypostcodelookups gp
STRAIGHT_JOIN uploads u ON gp.postal = u.postal
GROUP BY gp.postal;

2. 更新统计信息

手动更新表的统计信息，让优化器能准确估算行数：

• MySQL：ANALYZE TABLE uploads, germanypostcodelookups;
• PostgreSQL：ANALYZE uploads, germanypostcodelookups;

3. 优化uploads表的索引

创建(postal, id)复合索引，让数据库直接通过索引获取聚合所需数据，无需回表：

CREATE INDEX idx_uploads_postal_id ON uploads(postal, id);

如果原uploads_postal索引选择性极差，可考虑直接删除该冗余索引。

4. 校验关联字段一致性

确保germanypostcodelookups.postal与uploads.postal的字段类型、字符集、长度完全一致（比如同是VARCHAR(10)、utf8mb4）。类型不一致会触发隐式转换，导致索引失效，被迫大量过滤数据。

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