PostgreSQL 10.9 简单UPDATE语句执行耗时异常的性能排查
执行缓慢的根本原因
- 核心瓶颈来自IN子查询的重复行触发冗余扫描:子查询没有对返回的
"PrescriptionId"做去重,实际返回了2503751行存在大量重复的关联值,PostgreSQL 10对IN子查询的执行逻辑是逐行匹配外层表的主键索引,哪怕同一个Id被重复匹配上千次,也会独立触发上千次索引查找,累计这部分循环开销就达到45秒以上,占总耗时的85%,这也是执行计划中看到的Index Scan using "Prescription_pkey" ... loops=2503751的本质原因。 - 现有索引无法覆盖完整的过滤逻辑:
Discount表目前只有单列的PharmacyId、PrescriptionId索引,没有能同时匹配PharmacyId、DiscountBy、PrescriptionId三个字段的组合索引,子查询过滤时需要频繁回表读取无效行。Prescription表目前只有单列的PharmacyId索引,没有匹配DiscountList过滤条件的组合索引,外层过滤时需要回表读取整行判断值是否符合要求。
- 改写为JOIN后耗时更高的原因:直接JOIN的写法没有对
Discount表的明细行做去重,将250多万行的原始明细作为关联驱动表,进一步放大了重复匹配的循环开销,甚至因为重复行更多,导致耗时涨到178秒。
注:你已经完成VACUUM和ANALYZE操作,统计信息准确,不存在执行计划选择偏差的问题。
对应性能优化方案
1. 改写SQL执行逻辑,消除冗余循环
使用EXISTS替代原有的IN子查询,PostgreSQL对EXISTS的执行逻辑是找到第一个匹配记录就立即停止子查询扫描,完全不会因为子查询存在重复行触发冗余的外层主键查找,优化后的语句如下:
UPDATE "Prescription" p SET "DiscountList" = TRUE WHERE p."PharmacyId" = '1ec0cec5-1cbc-412f-9765-ac0f010de111' AND p."DiscountList" = FALSE AND EXISTS ( SELECT 1 FROM "Discount" d WHERE d."PrescriptionId" = p."Id" AND d."PharmacyId" = p."PharmacyId" AND d."DiscountBy" = 'Prescription' );
2. 补充适配场景的覆盖索引
基于现有SSD存储(已将random_page_cost设为1.0)的配置,建立两个精准匹配查询条件的组合索引,消除回表读取的开销:
-- Discount表覆盖索引:完全匹配子查询的关联和过滤字段,无需回表 CREATE INDEX CONCURRENTLY "idx_discount_pharmacy_discountby_pres" ON "Discount" ("PharmacyId", "DiscountBy", "PrescriptionId"); -- Prescription表覆盖索引:匹配外层WHERE的两个过滤字段,无需回表 CREATE INDEX CONCURRENTLY "idx_prescription_pharmacy_discountlist" ON "Prescription" ("PharmacyId", "DiscountList");
注:使用CONCURRENTLY创建索引不会锁表,适合生产环境执行。
完成上述调整后,整段UPDATE语句的执行耗时会从50秒以上降到100ms以内,即使没有需要更新的记录,也能在极短时间内完成全条件判断。
3. 避坑提示
- 所有涉及大表关联的子查询、JOIN逻辑,必须先对关联字段做去重/聚合,禁止直接将大表的原始明细行作为驱动表触发嵌套循环关联,否则极易产生数十万、上百万次的冗余扫描。
- 后续如果针对该UPDATE的执行频率很高,可以观察执行计划的回表情况,如果存在频繁回表读取整行的问题,在PostgreSQL 11及以上版本可以通过
INCLUDE子句将查询需要的非过滤字段加入索引,进一步降低IO开销。
内容的提问来源于stack exchange,提问作者brads256
相关产品推荐
相关产品推荐

