为何IN()子句中嵌套SELECT查询的性能开销极高？

问题背景 #

数据库表结构 #

create table orders (
         id integer, 
         primary key(id)
);
create table orderlines (
         id integer, 
         orderid integer, 
         primary key(id), 
         foreign key (orderid) references orders(id)
);

执行的两条查询语句 #

select * from orderlines where orderid in (1);
select * from orderlines where orderid in (select orderid from orders where orderid=1);

执行计划（explain结果） #

第一条查询的执行计划：

QUERY PLAN                          
------------------------------------------------------------
 Seq Scan on orderlines  (cost=0.00..38.25 rows=11 width=8)
   Filter: (orderid = 1)
(2 rows)

第二条查询的执行计划：

QUERY PLAN                                
------------------------------------------------------------------------
 Seq Scan on orderlines  (cost=0.00..47362.65 rows=1130 width=8)
   Filter: (SubPlan 1)
   SubPlan 1
     ->  Result  (cost=0.00..35.50 rows=2550 width=4)
           One-Time Filter: (orderlines.orderid = 1)
           ->  Seq Scan on orders  (cost=0.00..35.50 rows=2550 width=0)

疑问 #

我发现PostgreSQL会对orderlines表的每条记录执行子查询，这种方式效率极低。原本期望子查询先执行完成，再将结果以类似数组的形式传入IN()子句中。想知道查询优化器为何采用这种执行方式，以及如何优化该查询结构提升性能？

为什么查询优化器会选择这种执行方式？ #

核心原因是子查询存在字段引用错误，导致它被解析为相关子查询：

• 你的子查询select orderid from orders where orderid=1中，orders表并没有定义orderid字段（orders表只有id主键）。PostgreSQL在解析时会向上查找外部查询的表，把orderid解析为orderlines表的字段，从而让这个子查询变成了相关子查询——即子查询的结果依赖于外部查询的每条记录。
• 对于相关子查询，优化器无法提前计算出固定的结果集传入IN子句，只能遍历orderlines的每条记录，逐一执行子查询做判断。
• 从执行计划的One-Time Filter: (orderlines.orderid = 1)也能看出：PostgreSQL会先过滤orderlines中orderid=1的记录，但后续仍要对这些记录扫描整个orders表，进一步降低了效率。

如何优化查询结构提升性能？ #

1. 修正子查询的字段引用错误 #

将子查询中的字段改为orders表实际存在的id，让子查询变成独立子查询：

select * from orderlines where orderid in (select id from orders where id=1);

此时优化器会提前执行子查询得到固定结果集（比如1），再用这个结果过滤orderlines，执行计划会和第一条查询完全一致，效率大幅提升。

2. 改用JOIN替代IN子查询 #

对于关联查询，使用JOIN的写法逻辑更清晰，优化器也更容易生成高效的执行计划：

select ol.* 
from orderlines ol
join orders o on ol.orderid = o.id
where o.id = 1;

这种写法会让优化器优先利用orders表的主键索引快速定位到id=1的记录，再关联orderlines表，避免不必要的全表扫描。

3. 给orderlines.orderid添加索引 #

如果orderlines表数据量较大，给orderid字段添加B-tree索引可以避免全表扫描：

create index idx_orderlines_orderid on orderlines(orderid);

添加索引后，所有针对orderid的过滤或关联操作都会使用索引扫描，查询性能会得到质的提升。

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