PostgreSQL慢查询优化咨询：结合执行计划分析长查询执行过慢问题

性能瓶颈分析 #

• 最核心瓶颈是JOIN条件使用多OR逻辑：当前C表仅返回约3816行数据，关联的F子查询返回约188万行数据，OR条件导致无法使用索引匹配，每一行C表数据都要全量扫描188万行F表数据做条件匹配，嵌套循环连接总成本高达2.6亿，是耗时最高的部分。
• 子查询存在函数计算无法命中索引：F子查询中对BHF.external_key做了REPLACE去空格处理，没有预存计算结果的情况下无法使用普通索引，只能实时计算后匹配。
• 子查询所有表均为全表扫描：payments、bookings、booking_headers、booking_header_finances四张表均走顺序扫描，没有针对关联字段和过滤字段建索引，子查询本身执行成本就很高。
• 大结果集排序去重成本极高：查询末尾的DISTINCT需要对77万行、单行列宽169字节的结果集做全量排序，排序成本占总执行成本的近10%。

优化方案 #

• 改造JOIN逻辑拆分OR条件：将原来的OR关联拆分为三个独立的LEFT JOIN子查询，再用UNION ALL合并结果，避免OR导致的索引失效：

SELECT C.*, F.vp_booking_id, F.provider_id, F.campaign_code, F.external_key, F.total_price,
       CASE WHEN limonetik IS NULL OR limonetik != limonetix THEN NULL ELSE limonetix END AS limonetix,
       F.cancelled, F.eos_revenue
FROM th_flow.cancellations_big_file C
LEFT JOIN F ON C.external_key = F.external_key
WHERE C.external_key >= '5' AND C.external_key < 'A'
UNION ALL
SELECT C.*, F.vp_booking_id, F.provider_id, F.campaign_code, F.external_key, F.total_price,
       CASE WHEN limonetik IS NULL OR limonetik != limonetix THEN NULL ELSE limonetix END AS limonetix,
       F.cancelled, F.eos_revenue
FROM th_flow.cancellations_big_file C
LEFT JOIN F ON C.limonetik = F.limonetix AND C.limonetik IS NOT NULL AND C.amount = F.total_price
WHERE C.external_key >= '5' AND C.external_key < 'A'
UNION ALL
SELECT C.*, F.vp_booking_id, F.provider_id, F.campaign_code, F.external_key, F.total_price,
       CASE WHEN limonetik IS NULL OR limonetik != limonetix THEN NULL ELSE limonetix END AS limonetix,
       F.cancelled, F.eos_revenue
FROM th_flow.cancellations_big_file C
LEFT JOIN F ON C.new_external_key = F.external_key
WHERE C.external_key >= '5' AND C.external_key < 'A'

如果合并后结果存在重复，将UNION ALL改为UNION即可，比全局DISTINCT排序效率高很多。

• 预存计算字段并建索引：给booking_header_finances表加存储生成列存储去空格后的external_key，并针对关联字段建覆盖索引：

-- 加生成列预存去空格后的external_key
ALTER TABLE th_flow.booking_header_finances ADD COLUMN external_key_clean VARCHAR GENERATED ALWAYS AS (REPLACE(external_key, ' ', '')) STORED;
-- 建子查询覆盖索引，避免回表
CREATE INDEX idx_bhf_vp ON th_flow.booking_header_finances(vp_booking_id) INCLUDE (provider_id, campaign_code, external_key_clean, total_price, is_canceled_override, is_canceled, eos_revenue);
CREATE INDEX idx_bh_vp ON th_flow.booking_headers(vp_booking_id) INCLUDE (removed);
CREATE INDEX idx_bookings_vp ON th_flow.bookings(vp_booking_id) INCLUDE (booking_id);
CREATE INDEX idx_payments_booking ON th_flow.payments(booking_id) INCLUDE (external_key);
-- 如果使用临时表存储F子查询结果，额外给关联字段建索引
CREATE INDEX idx_f_external ON F(external_key);
CREATE INDEX idx_f_limonetix_total ON F(limonetix, total_price);

• 优化模糊过滤逻辑：BHF.campaign_code NOT LIKE '%PFS%'前后通配无法走普通索引，如果该条件过滤比例很高，可以新增has_pfs布尔标志字段预存判断结果，加索引后直接过滤即可。
• 移除不必要的DISTINCT：拆分OR关联后如果没有重复行，直接去掉DISTINCT即可，省掉大结果集排序开销。

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