PostgreSQL同逻辑SELECT与DELETE执行计划不同 执行耗时悬殊

PostgreSQL对DELETE和SELECT语句使用的成本估算模型存在差异，相同筛选逻辑下出现执行计划分化是常见现象：

• SELECT无数据修改开销，优化器会选择token_utxo_output_tx_time二级索引范围扫描+哈希反连接的高效路径：仅扫描时间范围内的目标行，再和ma_tx_out做存在性判断，扫描数据量极小，因此执行速度快。
• DELETE需要额外承担元组标记删除、所有关联索引条目维护、WAL日志写入的开销，优化器默认会高估二级索引回表操作的成本，很容易错误选择全表扫描token_utxo再关联ma_tx_out的次优路径，扫描数据量会放大几个数量级，导致执行极慢。
• 原始SQL中时间条件使用(select 'xxx'::timestamptz)的无意义标量子查询包装，会干扰DELETE路径选择阶段的选择率估算，进一步放大成本计算偏差，属于不必要的写法。

按改造成本从低到高排列：

1. 写法调整（零成本，优先选择） #

去掉时间条件的标量子查询包装，通过CTE先物化待删除行的主键ID，强制优化器先走和SELECT一致的筛选逻辑，再通过主键定位删除，执行计划稳定性极高：

WITH to_delete AS (
    SELECT id
    FROM processed.token_utxo
    WHERE output_tx_time >= '2022-03-01T00:00:00+00:00'::timestamptz
      AND output_tx_time < '2022-03-02T00:00:00+00:00'::timestamptz
      AND NOT EXISTS (
          SELECT 1
          FROM public.ma_tx_out
          WHERE ma_tx_out.id = token_utxo.id
      )
)
DELETE FROM processed.token_utxo
WHERE id IN (SELECT id FROM to_delete);

2. 会话级参数调整（不需要修改SQL） #

如果不想调整业务SQL，可以在执行DELETE前临时设置会话级成本参数，修正优化器的成本估算偏差，适合SSD存储的实例：

-- 仅当前会话生效，不需要修改全局数据库配置
SET random_page_cost = 1.1;
SET cpu_tuple_cost = 0.03;

参数设置后再执行原DELETE语句，优化器会倾向于选择二级索引扫描的高效路径。

3. 临时表方案（适合大表批量删除场景） #

如果单次删除的数据量超过表总数据量的5%，可以通过临时表中转待删ID，避免长事务持锁、计划不稳定的问题：

-- 创建临时表存储待删ID，事务提交后自动删除
CREATE TEMP TABLE tmp_del_ids ON COMMIT DROP AS
SELECT t.id
FROM processed.token_utxo t
LEFT JOIN public.ma_tx_out m ON t.id = m.id
WHERE t.output_tx_time >= '2022-03-01T00:00:00+00:00'::timestamptz
  AND t.output_tx_time < '2022-03-02T00:00:00+00:00'::timestamptz
  AND m.id IS NULL;

-- 收集临时表统计信息，保证关联计划准确
ANALYZE tmp_del_ids;

-- 通过主键关联删除，效率最高
DELETE FROM processed.token_utxo t
USING tmp_del_ids d
WHERE t.id = d.id;

执行优化后的SQL前，先通过EXPLAIN ANALYZE确认执行计划符合预期：

• 筛选阶段命中token_utxo_output_tx_time索引做范围扫描
• 和ma_tx_out的关联使用哈希反连接/哈希左连接，无全表扫描
• 最终删除阶段命中token_utxo主键索引做定位

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