咨询：PostgreSQL相似查询为何存在巨大性能差异

表结构与环境 #

创建的两张表结构如下：

create table tableA (id int not null primary key);
create table tableB (id int not null primary key, tableA_id int null, 
foreign key(tableA_id) references tableA(id));

• 在tableB.tableA_id字段上创建了索引
• 两张表各存储有约5000万条数据

需求与查询对比 #

需求：查询tableA中所有未在tableB中出现关联的行。

初始查询（耗时11小时） #

select a.* from tableA a left join tableB b on a.id = b.tableA_id where b.id is null

优化后查询（耗时15分钟） #

select a.* from tableA a left join tableB b on a.id = b.tableA_id where b.tableA_id is null

性能差异原因与实质区别 #

1. 索引利用效率差异 #

• 初始查询的where b.id is null：b.id是tableB的主键，本身不会为null，只有左连接无匹配时才会被填充为null。这个条件无法关联到tableB.tableA_id上的索引，数据库只能先完成全量左连接，再逐一过滤结果集，相当于要处理两张表的全量关联数据，效率极低。
• 优化后查询的where b.tableA_id is null：这个条件直接对应已创建的tableB.tableA_id索引。数据库可以通过该索引快速定位tableB中无匹配的关联记录，或者直接反向匹配tableA中不在tableB关联范围内的行，全程利用索引减少数据扫描量。

2. 执行计划逻辑差异 #

• 初始查询的执行路径：先对tableA和tableB做全量左连接，生成包含所有匹配/不匹配的中间结果集，再遍历整个结果集筛选b.id为null的行。5000万级别的数据量会导致中间结果集异常庞大，遍历过滤的成本极高。
• 优化后查询的执行路径：数据库会优先利用tableB.tableA_id索引，快速获取tableB中所有存在的tableA_id值，再通过反向匹配直接找出tableA中不在该集合内的行；或者在左连接过程中，通过索引实时判断是否存在匹配项，无需生成全量中间结果，大幅减少计算量。

3. 实质区别 #

两个查询的最终结果逻辑等价，但执行逻辑的核心差异在于：

• b.id is null是通过主键的空值来间接判断左连接无匹配，无法触发关联字段的索引，导致全量数据处理。
• b.tableA_id is null直接针对关联字段的空值状态判断，能够触发预创建的索引，让数据库采用更高效的“查找无匹配行”执行路径，从而将耗时从小时级压缩到分钟级。

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