PostgreSQL中SQL无语法错误却异常缓慢的原因咨询

问题现象 #

一段存在逻辑错误的SQL未触发语法错误，反而运行耗时长达16秒，修正后的SQL仅需100ms即可完成。

错误SQL #

with s as (
    select u.order_id from order u        -- 错误：应选择u.product_id
    join product n on u.product_id=n.product_id
    where n.created_at < current_timestamp - interval '30 days' 
)
delete from order
where product_id in (select product_id from s);   

该SQL的CTE仅查询了u.order_id，但后续子查询引用了product_id，按预期PostgreSQL应提示语法错误（CTE中不存在product_id字段），但实际未报错且执行了。

修正后的SQL #

with s as (
    select u.product_id from order u
    join product n on u.product_id=n.product_id
    where n.created_at < current_timestamp - interval '30 days'
)
delete from order
where product_id in (select product_id from s);

环境信息 #

select version();
-- PostgreSQL 14.9 on aarch64-unknown-linux-gnu, compiled by aarch64-unknown-linux-gnu-gcc (GCC) 9.5.0, 64-bit
select count(*) from product;   
-- 11
select count(*) from order;     
-- 156

原因分析 #

这是PostgreSQL的列名解析规则导致的：当子查询中引用的列在外层查询的表中存在时，PostgreSQL会优先解析为外层表的列，而非报错。

在错误SQL的where product_id in (select product_id from s)中，子查询里的product_id并未取自CTE s，而是被解析成了外层delete from order语句中的order.product_id。等效于执行了：

delete from order
where product_id in (select order.product_id from s);

由于CTE s返回了所有符合条件的order_id，这个子查询会将order表的每一行与s的每一行进行笛卡尔积匹配，产生大量无效计算，因此耗时极长。

规避方案 #

• 用表别名显式限定列名：在子查询中明确指定列所属的CTE/表别名，比如将子查询改为select s.product_id from s，这样PostgreSQL会直接检查s中是否存在该列，不存在则触发语法错误。
• 开启sql_safe_updates参数：该参数可防止无WHERE条件或WHERE条件可能导致全表更新的操作，虽不能直接检测列名解析歧义，但能降低误操作风险。
• 单独验证CTE结果：编写SQL后先单独运行CTE部分，确认返回的列和数据符合预期，再整合到完整语句中。
• 用显式JOIN替代IN子查询：将DELETE语句改写为JOIN形式，列的归属更清晰，减少解析歧义：

delete from order o
using (
    select u.product_id from order u
    join product n on u.product_id=n.product_id
    where n.created_at < current_timestamp - interval '30 days'
) s
where o.product_id = s.product_id;

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