PostgreSQL查询性能调优：千万级大表关联查询耗时优化

完全可以实现查询耗时降低至1秒以内，核心优化思路是避免全表扫描4800万行的Table_S2，通过覆盖索引将查询IO降到极低水平。

1. 新增覆盖索引（成本最低，见效最快，实施后基本可达1秒以内要求） #

覆盖索引可以让查询完全不需要访问表的主存储，仅通过索引就能拿到所有需要的字段，IO量会降低几个数量级：

• 在Table_A上创建联合索引 (A_Name, A_ID)：可以直接通过A_Name = 'IJK'的过滤条件拿到对应的A_ID，无需回表查询其他字段
• 在Table_S1上创建联合索引 (A_ID, S_ID)：拿到目标A_ID后，可以直接索引扫描得到所有符合条件的S_ID，无需回表
• 在Table_S2上创建联合索引 (S_ID, S2_Date_1, S2_Date_2)：这是核心优化点，关联S_ID时直接从索引中读取日期字段，不需要访问Table_S2主表，且索引有序的特性可以大幅加快DISTINCT去重的效率，不需要扫描完所有1800万条对应A_ID=22的S2记录，只要遍历到所有出现过的年份即可终止计算。

2. 预计算年份字段（进一步压缩耗时，适合长期使用场景） #

如果查询需求固定需要提取年份，可以直接在Table_S2新增两个预计算的整数类型字段：

• S2_Year1：存储S2_Date_1对应的年份，数据写入/更新时自动计算赋值
• S2_Year2：存储S2_Date_2对应的年份，数据写入/更新时自动计算赋值
  同时将Table_S2的覆盖索引调整为(S_ID, S2_Year1, S2_Year2)，查询时直接读取预存的年份值，省去每次查询时的EXTRACT函数计算开销，性能可以再提升30%以上。

3. 查询语句优化 #

将原语句的隐式连接写法改为显式内连接，避免不必要的笛卡尔积风险，优化后的语句如下：

SELECT DISTINCT EXTRACT(YEAR FROM s2.S2_Date_1)
FROM Table_A a
INNER JOIN Table_S1 s1 ON a.A_ID = s1.A_ID
INNER JOIN Table_S2 s2 ON s1.S_ID = s2.S_ID
WHERE a.A_Name = 'IJK';

注：原查询语句中出现的s1.B_ID = b.B_ID条件不存在对应的b表，属于冗余错误条件，需要删除

4. 可选进阶优化（适合大数据量长期运维场景） #

如果后续还有更多类似查询，可以对Table_S2按S_ID做哈希分区，将关联查询的扫描范围进一步缩小到对应分区，性能会有额外提升，但改动成本高于新增索引，优先做前三项优化即可满足要求。

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