PostgreSQL跨表JOIN时OR条件查询索引失效如何优化

问题本质 #

你遇到的性能瓶颈核心是PostgreSQL查询优化器的谓词下推限制：跨两张关联表的OR过滤条件无法被拆分到JOIN动作之前分别执行，只能先完成全表JOIN生成中间结果集后再做过滤，因此无法命中两张表的独立索引。

可落地优化方案 #

• 查询改写为UNION模式
  把OR逻辑拆分为两个独立子查询的合并，改写后两个子查询均可独立命中各自的Trigram索引，性能提升最明显，改写示例如下：

  SELECT id FROM table1 WHERE haystack ILIKE '%needle%'
  UNION
  SELECT table1.id 
  FROM table1
  JOIN table2 ON table1.fk = table2.id
  WHERE table2.haystack ILIKE '%needle%'
  

  UNION会自动对两个结果集去重，返回的结果和原LEFT JOIN+OR的查询结果完全一致。如果确认不会有重复id，也可以用UNION ALL替代UNION获得更高性能。
• 使用物化视图预关联
  对实时性要求不高的场景，可以创建预关联两张表的物化视图，给视图上的两个haystack字段分别建Trigram索引，定期刷新物化视图即可，查询直接查物化视图无需运行时关联。
• 轻量冗余字段同步
  不用全表反规范化，仅在table1上新增一个冗余字段存储关联的table2.haystack值，用触发器在table2.haystack变更时同步更新table1的冗余字段，之后就可以直接查询table1单表的两个字段OR条件，能同时命中联合索引或者两个独立索引。
• 执行计划提示（适配高版本PostgreSQL）
  如果你使用PostgreSQL 12及以上版本，可以在测试后给查询添加BitmapOr的执行计划提示，强制优化器尝试分别扫描两个索引后合并结果，不过该方案稳定性不如查询改写，不建议生产环境优先使用。

内容的提问来源于stack exchange，提问作者d-vine