PostgreSQL 10.3关联查询运行过慢问题求助

首先，针对你这个350万级别的表关联查询，核心问题大概率出在索引设计不匹配查询需求以及可能的执行计划选择上，我给你一步步拆解优化方案：

1. 重构索引，让查询用上高效的覆盖索引 #

你的现有索引并没有贴合当前查询的过滤+关联逻辑，这是性能瓶颈的主要原因：

针对tb_two的索引优化 #

当前的tb_two_idx_1是(Source, attr5)，但你的查询只需要过滤Source，然后拿到tb_hit_hitid去关联tb_one，完全不需要attr5。所以应该创建一个过滤+关联字段的覆盖索引：

CREATE INDEX tb_two_source_hitid_idx ON tb_two (source ASC, tb_hit_hitid ASC);

这个索引能让PostgreSQL直接从索引里筛选出符合source IN ('source1','source2')的记录，并直接获取关联用的tb_hit_hitid，不需要回表查询原数据块，大幅减少IO开销。

针对tb_one的索引优化 #

你的查询需要通过tb_hit_hitid关联，然后返回attr1-4。PostgreSQL 10支持INCLUDE子句创建轻量覆盖索引，所以可以创建：

CREATE INDEX tb_one_hitid_attrs_idx ON tb_one (tb_hit_hitid ASC) INCLUDE (attr1, attr2, attr3, attr4);

这个索引以关联字段tb_hit_hitid为索引键，同时把需要返回的字段包含在索引中，这样关联后直接从索引里取结果，不用回表扫描tb_one的主数据，性能会提升很多。如果不想用INCLUDE，也可以把attr1-4都加到索引键里，但这样索引体积会更大，INCLUDE是更优的选择。

2. 检查执行计划，调整执行策略 #

先运行EXPLAIN ANALYZE查看实际执行计划：

EXPLAIN ANALYZE
SELECT thf.attr1, thf.attr2, thf.attr3, thf.attr4 
FROM tb_one AS thf 
INNER JOIN tb_two AS ths ON ths.tb_hit_hitid = thf.tb_hit_hitid 
WHERE ths.source IN ('source1', 'source2');

重点关注这几点：

• 如果执行计划是Hash Join：检查work_mem的设置，默认的work_mem可能太小（比如4MB），导致哈希表写到磁盘上，变慢。可以临时调大试试：

  SET work_mem = '64MB'; -- 根据你的服务器内存调整，比如16GB内存的话可以设到64-128MB
  

  然后重新跑查询看性能变化。
• 如果执行计划是Nested Loop：那说明优化器认为过滤后的tb_two结果集很小，这时候如果结果集其实很大，那嵌套循环会很慢，可能需要临时关闭嵌套循环测试性能（不推荐长期启用该设置）：

  SET enable_nestloop = off;
  

• 如果执行计划是Merge Join：确保两个表的关联字段都有有序索引（我们上面创建的索引已经满足），这样合并连接的效率会很高。

3. 确保统计信息准确 #

大表的数据如果有频繁的插入/更新，统计信息可能过时，导致优化器选错执行计划。更新统计信息：

ANALYZE tb_one;
ANALYZE tb_two;

4. 检查关联字段的数据类型一致性 #

一定要确认tb_one.tb_hit_hitid和tb_two.tb_hit_hitid的数据类型完全一致（比如都是int8或者varchar(32)）。如果类型不一致，PostgreSQL会做隐式类型转换，导致索引失效，关联时只能全表扫描，这会严重拖慢性能。

按照上面的步骤调整后，你的查询性能应该会有明显的提升。如果还是有问题，可以把EXPLAIN ANALYZE的结果贴出来，我们再进一步分析。

内容的提问来源于stack exchange，提问作者Wandré Veloso