SQL中UNION与OR运算符查询计划差异显著的原因

核心是PostgreSQL查询优化器对带子查询的OR分支条件存在优化局限性，无法自动生成最优执行路径，具体逻辑如下：

• 带OR的原始语句无法触发索引拆分优化
  优化器处理id = 'dummy' or id in (select id from table2 where id = 'dummy')这类条件时，不会自动把OR两侧的判断逻辑拆成独立的可走索引的查询分支。从执行计划可以看到，它的执行逻辑是：先执行table2的子查询把匹配结果存成哈希集合，再对table1做全表顺序扫描，逐行判断当前行是否满足「id等于'dummy'」或「id在子查询返回的哈希集合中」。
  这里优化器的行数预估严重失真：它预判过滤后会返回25万+行，基于这个预估判断全表扫描的代价比走索引回表更低，但实际子查询没有返回任何结果，全表扫描51万行的IO开销全部浪费，最终耗时达到557ms。
  注意：优化器自动把OR条件拆成多分支索引扫描的优化，仅支持OR两侧都是同表常量过滤条件的场景（比如where id=1 or id=2会自动生成BitmapOr索引扫描路径），一旦OR某一侧涉及子查询、跨表关联条件，优化器受改写逻辑复杂度、边界场景（比如NULL值判断、结果去重）的限制，不会自动做这个转换。
• UNION写法相当于手动完成了优化器做不到的路径拆分
  你手动把OR条件拆成两个独立查询用UNION拼接，相当于跳过了优化器的改写限制，直接给了它可以分别做索引优化的两个简单查询：
  • 第一个分支id = 'dummy'是明确的常量等值查询，优化器直接走table1的id索引做单点扫描，仅命中1行数据
  • 第二个分支的IN子查询被优化器改写成嵌套循环关联：先走table2的索引找匹配'dummy'的id（实际返回0行），后续table1的主键索引查找因为没有输入值完全不需要执行
  • 最后两个分支的结果做快速排序去重（UNION默认带去重逻辑），因为总数据量只有1行，排序仅占用25kB内存，开销可以忽略
    整个执行流程没有全表扫描，所有数据访问都是索引点查，总耗时仅0.13ms是完全符合预期的。

补充：如果不想改写SQL，也可以尝试把语句调整为id = 'dummy' or id in ('这里直接放子查询查出的固定值')，但如果子查询结果是动态变化的，手动拆UNION是最稳定的优化方案。

内容的提问来源于stack exchange，提问作者Spring boot progammer