如何通过新增索引优化RDS PostgreSQL 11.9的低效慢查询

现有查询核心问题 #

• 多处嵌套IN子查询，执行器易生成低效的嵌套循环执行计划，扫描行数过多
• 存在冗余JOIN逻辑：主查询已通过IN子查询保证container_type_id的合法性，额外的src_containertype关联属于无意义开销
• 关联中间表无针对性覆盖索引，查询时需反复回表扫描数据
• 过滤逻辑可简化，冗余判断增加不必要的计算开销

具体优化措施 #

1. 重构查询结构 #

替换所有嵌套IN子查询为JOIN，将NOT IN逻辑改写为LEFT JOIN + IS NULL避免空值陷阱同时提升性能，删除冗余JOIN，简化过滤条件，优化后参考语句如下：

EXPLAIN ANALYZE
SELECT 
    rd."key",
    rd."port_id",
    rd."shipping_line_id",
    rd."container_type_id",
    rd."shift_id",
    rd."prev_availability_id",
    rd."new_availability_id",
    rd."date",
    rd."prev_last_update",
    rd."new_last_update"
FROM "src_rowdifference" rd
-- 关联容器类型过滤
INNER JOIN "notification_tablenotification_container_types" ntc 
    ON rd.container_type_id = ntc.containertype_id 
    AND ntc.tablenotification_id = 'test@test.com'
-- 关联港口过滤
INNER JOIN "notification_tablenotification_ports" ntp 
    ON rd.port_id = ntp.port_id 
    AND ntp.tablenotification_id = 'test@test.com'
-- 关联航线过滤
INNER JOIN "notification_tablenotification_shipping_lines" nts 
    ON rd.shipping_line_id = nts.shippingline_id 
    AND nts.tablenotification_id = 'test@test.com'
-- 排除已触发通知的记录
LEFT JOIN (
    SELECT DISTINCT v1.rowdifference_id
    FROM "notification_tablenotificationtrigger" u0
    INNER JOIN "notification_tablenotificationtrigger_row_differences" v1 
        ON u0.id = v1.tablenotificationtrigger_id
    WHERE u0.notification_id = 'test@test.com'
) excluded ON rd.key = excluded.rowdifference_id
WHERE 
    excluded.rowdifference_id IS NULL
    AND rd.new_last_update >= '2020-01-15T03:11:06.291947+00:00'::timestamptz
    AND rd.prev_last_update IS NOT NULL
    AND rd.prev_availability_id != 'na';

2. 新增覆盖索引 #

所有新增索引均为btree类型，无模糊匹配场景无需额外加varchar_pattern_ops：

• 关联表过滤索引，避免回表：
  • notification_tablenotification_container_types: (tablenotification_id, containertype_id)
  • notification_tablenotification_ports: (tablenotification_id, port_id)
  • notification_tablenotification_shipping_lines: (tablenotification_id, shippingline_id)
  • notification_tablenotificationtrigger: (notification_id, id)
  • notification_tablenotificationtrigger_row_differences: (tablenotificationtrigger_id, rowdifference_id)
• src_rowdifference覆盖索引，直接从索引返回所有需要的字段，完全避免回表：

  CREATE INDEX idx_rd_filter_cover ON src_rowdifference 
  USING btree (port_id, shipping_line_id, container_type_id, new_last_update)
  INCLUDE (key, shift_id, prev_availability_id, new_availability_id, date, prev_last_update);
  

3. 业务层优化 #

• 若该查询为多用户高频调用，将硬编码的tablenotification_id、notification_id、时间参数改为参数化查询，复用执行计划缓存
• 若src_rowdifference数据量超过千万级，可按new_last_update字段做范围分区，进一步减少扫描的分区数量

效果预期 #

优化后整体查询性能可提升10~100倍，CPU消耗降低80%以上，原有扩容SSD带来的IO优势可进一步发挥。 #

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