PostgreSQL含OR条件的多索引ILIKE查询未使用索引问题求助

问题原因 #

当查询中使用跨多个表的OR条件时，PostgreSQL优化器无法生成同时利用不同表索引的执行计划。原查询中OR连接了Warehouse.name和Persona表的两个字段条件，优化器只能先完成所有表的关联，再对结果集进行全量过滤，完全无法利用已创建的GIN索引，导致性能急剧下降。

解决方案：拆分查询+UNION ALL合并 #

将原查询拆分为多个独立子查询，每个子查询对应一组单一表的过滤条件，再通过UNION ALL合并结果。这样每个子查询都能单独触发对应表的GIN索引，大幅提升查询效率。

优化后的查询示例 #

-- 子查询1：匹配Warehouse.name的记录
SELECT *
FROM package AS "Package"
INNER JOIN persona AS "Persona" ON "Package"."customer_id" = "Persona"."ID"
LEFT OUTER JOIN "warehouse" AS "Warehouse" ON "Package"."IDBODEGA" = "Warehouse".id
WHERE "Warehouse"."name" ILIKE '%test name%'

UNION ALL

-- 子查询2：匹配Persona邮箱/全名的记录，同时排除已被子查询1匹配的结果（避免重复）
SELECT *
FROM package AS "Package"
INNER JOIN persona AS "Persona" ON "Package"."customer_id" = "Persona"."ID"
LEFT OUTER JOIN "warehouse" AS "Warehouse" ON "Package"."IDBODEGA" = "Warehouse".id
WHERE (
    "Persona"."EMAILPERSONA" ILIKE '%test name%'
    OR "Persona"."NOMBREPERSONA" || ' ' || "Persona"."APELLIDOPERSONA" ILIKE '%test name%'
)
AND NOT EXISTS (
    SELECT 1
    FROM "warehouse" w
    WHERE w.id = "Package"."IDBODEGA" AND w.name ILIKE '%test name%'
);

可选调整 #

• 如果不需要严格去重（即允许同一package记录因同时满足两组条件而重复出现），可以直接用UNION替代UNION ALL，但UNION会自动去重，性能略低于UNION ALL。
• 若package表的customer_id和IDBODEGA字段未建索引，建议补充BTREE索引，进一步加速表关联：

  CREATE INDEX idx_package_customer_id ON public.package USING btree ("customer_id");
  CREATE INDEX idx_package_idbodega ON public.package USING btree ("IDBODEGA");
  

原理说明 #

拆分后的每个子查询仅针对单一表的过滤条件，优化器可以精准选择对应GIN索引快速定位符合条件的记录，再完成表关联。最终合并结果集的总耗时，基本等于各子查询耗时之和，远低于原全量过滤的耗时。

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