查询参数细微差异导致PostgreSQL查询耗时悬殊问题排查

为什么两个查询耗时差异如此悬殊？ #

从你提供的ANALYZE执行结果来看，慢查询的核心问题出在嵌套循环（Nested Loop）的执行逻辑上：

• 优化器先扫描part_masters表，过滤出符合combo条件的262行数据。
• 紧接着对这262行的每一行，都要全表扫描一次locations表（累计循环262次），来匹配ubicacion LIKE '%P01%'的条件。每次全表扫描要处理约3.9万行数据，262次循环下来总扫描量超过1000万行，这直接拖慢了查询速度。

而查询'%P0%'时更快，大概率是因为优化器选择了更高效的执行计划（比如先扫描locations找到匹配行，再和part_masters做Hash Join）——这是因为PostgreSQL根据统计信息判断，'%P0%'匹配的locations行数更少，先扫locations更划算；但对于'%P01%'，优化器错误地选择了嵌套循环，再加上locations没有索引，导致全表扫描的重复执行，最终引发超时。

另外，你创建的part_masters_on_combo_idx trigram索引没被使用，是因为查询中用的是unaccent(combo)做模糊匹配，而你的索引是直接建在combo字段上的，两者不匹配，优化器无法利用这个索引。

优化方案 #

1. 为locations.ubicacion创建基于unaccent的trigram索引

这是解决慢查询的核心，让ubicacion的模糊匹配不再依赖全表扫描：

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX locations_on_unaccent_ubicacion_idx ON locations USING gin (unaccent(ubicacion) gin_trgm_ops);

2. 调整part_masters的trigram索引，适配unaccent查询

修改现有索引，让combo的模糊过滤也能用上索引：

-- 先删除旧索引（如果不需要保留的话）
DROP INDEX IF EXISTS part_masters_on_combo_idx;
-- 创建基于unaccent(combo)的trigram索引
CREATE INDEX part_masters_on_unaccent_combo_idx ON part_masters USING gin (unaccent(combo) gin_trgm_ops);

3. 更新统计信息，帮助优化器选对执行计划

执行以下命令让PostgreSQL获取最新的表统计数据，避免优化器做出错误的计划选择：

ANALYZE locations;
ANALYZE part_masters;

4. 可选：调整查询逻辑，减少不必要的开销

如果一个part_master对应多个location导致LEFT JOIN产生重复行，你可以用EXISTS替代LEFT JOIN + DISTINCT，省去排序和去重的成本：

SELECT part_masters.* 
FROM part_masters 
WHERE EXISTS (
    SELECT 1 FROM locations 
    WHERE locations.sap_cod = part_masters.sap_cod 
      AND unaccent(locations.ubicacion) ILIKE unaccent('%P01%')
)
AND unaccent(part_masters.combo) ILIKE unaccent('%junta%')
AND unaccent(part_masters.combo) ILIKE unaccent('%torica%')
ORDER BY part_masters.sap_cod ASC;

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