PostgreSQL查询首次运行耗时过长，寻求优化方案

从你提供的EXPLAIN ANALYZE结果来看，主要性能瓶颈集中在大量无效嵌套循环关联和大表扫描高耗时上，以下是具体优化方向：

1. 重构半连接逻辑，减少无效索引查找 #

当前执行计划先扫描ship表（单worker返回13719行，总计41157行），再逐个与package表做半连接，但实际仅557行ship能匹配到edd_date='2023-08-30'的package记录，剩余4万多次索引查找完全无效。

• 调整查询驱动顺序，先从package表过滤目标日期的行，再关联ship表筛选admin_loc='ABC'的记录：

  SELECT [你需要的字段]
  FROM package p
  JOIN ship s ON p.ship_uid = s.uid
  WHERE p.edd_date = '2023-08-30'::date
    AND s.admin_loc = 'ABC'::text
  -- 后续关联address、add_aefs的逻辑
  

• 给package表创建联合索引，加速日期过滤和关联：

  CREATE INDEX idx_package_edd_shipuid ON package(edd_date, ship_uid);
  

2. 提前过滤add_aefs的无效行 #

当前计划中add_aefs关联执行了1667次，但每次都因geo_rank>100过滤掉所有行，属于纯无用开销。

• 把geo_rank>100条件直接加到关联语句中，让数据库提前过滤：

  SELECT [你需要的字段]
  FROM ...
  JOIN address a ON ...
  JOIN add_aefs ae ON ae.share = a.share AND ae.geo_rank > 100
  -- 其他条件
  

• 创建带过滤条件的部分索引，直接跳过不符合要求的行：

  CREATE INDEX idx_add_aefs_share_georank ON add_aefs(share) WHERE geo_rank > 100;
  

3. 更新表统计信息 #

执行计划中ship表预估行数（3171行）与实际行数（13719行/worker）偏差极大，会导致优化器生成不合理的执行计划，更新统计信息：

ANALYZE ship;
ANALYZE package;
ANALYZE address;
ANALYZE add_aefs;

4. 调整并行扫描参数 #

ship表的Parallel Bitmap Heap Scan耗时占比很高，可尝试临时提高并行worker数量（根据服务器CPU核心数调整，比如设为4）：

SET max_parallel_workers_per_gather = 4;

测试有效后，可考虑在postgresql.conf中永久调整。

5. 消除隐式类型转换 #

执行计划中出现(admin_loc)::text = 'ABC'::text的隐式类型转换，可能影响索引高效利用：

• 若admin_loc字段无需当前类型，直接修改为text类型；
• 若无法修改字段类型，创建函数索引适配类型转换：

  CREATE INDEX idx_ship_admin_loc_text ON ship((admin_loc)::text);
  

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