为何将带OR的PostgreSQL DELETE查询拆分为两个单独查询更优？

问题背景 #

在PostgreSQL 14.5的大表task_dependency中，需通过task_id和depends_on_task_id两列删除数据，测试了三种执行方案：

• 单条带OR的DELETE（初始状态）：

  delete from task_dependency where task_id = 1 or depends_on_task_id = 1;
  

  此时仅存在包含两列的唯一索引，无单独列索引，执行计划成本为0-1241500。

• 创建单独列索引后的单条DELETE：
  先创建单列索引：

  CREATE INDEX task_dependency_task_id_idx ON task_dependency (task_id);
  CREATE INDEX task_dependency_depends_on_task_id_idx ON task_dependency (depends_on_task_id);
  

  再执行原DELETE语句，执行计划成本降至32-5316，但采用的是两次Bitmap Index Scan而非Index Scan。

• 拆分为两条独立DELETE：

  delete from task_dependency where task_id = 1;
  delete from task_dependency where depends_on_task_id = 1;
  

  执行计划成本进一步降至1-672，且为Index Scan。

核心疑问：为何数据库无法自动将带OR的查询转为两次Index Scan？是否有办法优化单条查询，或必须拆分？

原因解析 #

1. 交集去重的约束 #

带OR的单条DELETE需要一次性处理所有符合条件的行，包括同时满足task_id=1和depends_on_task_id=1的交集数据。如果直接用两次Index Scan合并结果，必须额外做去重操作避免重复删除同一行。而Bitmap Index Scan通过位图的OR操作可以高效合并两个索引的结果，同时天然避免重复扫描同一行，这是优化器优先选择它的核心原因。

2. 成本估算的选择 #

PostgreSQL优化器会对比不同执行路径的成本。对于大表，Bitmap Scan处理OR条件时，合并位图的成本通常低于两次Index Scan加去重的成本，因此优化器会选择Bitmap Scan而非两次独立的Index Scan。

3. Index Scan的特性 #

两次独立的DELETE本质是两个独立的查询，第一次删除后，交集数据已经被移除，第二次不需要再处理去重。但单条带OR的DELETE无法拆分这种独立执行逻辑，必须在一次操作中完成所有行的匹配和删除。

单条查询的优化方案 #

如果希望保持单条DELETE语句的形式，可以尝试以下方法：

1. 用UNION构造去重后的目标集 #

通过UNION获取所有需要删除的行的主键（或唯一标识），再执行DELETE：

DELETE FROM task_dependency
WHERE id IN (
    SELECT id FROM task_dependency WHERE task_id = 1
    UNION
    SELECT id FROM task_dependency WHERE depends_on_task_id = 1
);

这种写法会触发两次Index Scan，再通过UNION去重，实际执行成本需根据数据量测试。如果确定无交集数据，可改用UNION ALL进一步降低成本。

2. 强制关闭Bitmap Scan（不推荐） #

通过临时修改优化器参数强制走Index Scan，但这种方式可能因手动干预导致执行效率下降，仅适合测试场景：

SET enable_bitmapscan = off;
delete from task_dependency where task_id = 1 or depends_on_task_id = 1;
SET enable_bitmapscan = on;

是否必须拆分？ #

追求最低执行成本的话，拆分两条DELETE是最优选择：

• 两次独立的Index Scan无需处理交集去重，逻辑简单，执行计划成本更低。
• 代码可读性高，维护成本低，适合生产环境使用。

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