PostgreSQL 10.4反连接查询未使用索引，求优化方案

问题场景 #

执行以下查询时：

select * from t1 where i not in (select j from t2)

预期会利用t2.j上的索引，但实际执行计划显示对t2做了全表扫描：

Seq Scan on t1  (cost=169.99..339.99 rows=5000 width=4)
  Filter: (NOT (hashed SubPlan 1))
  SubPlan 1
    ->  Seq Scan on t2  (cost=0.00..144.99 rows=9999 width=4)

测试用表的创建语句：

create table t1(i integer);
insert into t1(i) select s from generate_series(1, 10000) s;
create table t2(j integer);
insert into t2(j) select s from generate_series(1, 9999) s;
create index index_j on t2(j);

在百万级数据量的表中遇到同类问题时，全表扫描仅返回数百条数据，查询速度极慢。

核心结论 #

PostgreSQL完全支持反连接使用索引，未使用索引是优化器基于成本估算做出的选择，而非不支持或配置遗漏。

原因分析 #

1. 小表成本估算：测试中的t2仅9999行，全表扫描的IO成本远低于索引扫描（索引需要额外的索引页读取，即使是覆盖索引，PostgreSQL的成本模型默认认为全表扫描小表更高效）。
2. 哈希子计划的优势：优化器选择哈希子计划是因为构建哈希表后可以快速过滤t1的数据，对于t1全表扫描的场景，这种方式的整体成本被估算为更低。

解决方法 #

针对百万级表的慢查询问题，可尝试以下方案：

1. 改写查询语句 #

将NOT IN改写为左连接+空值过滤，这种写法更容易让优化器选择索引扫描：

select t1.* 
from t1 
left join t2 on t1.i = t2.j 
where t2.j is null;

2. 强制使用索引 #

通过索引提示引导优化器使用t2.j上的索引（PostgreSQL 9.6及以上版本支持）：

select * from t1 
where i not in (select j from t2 index index_j);

或者临时禁用全表扫描（仅用于测试，不建议全局配置）：

set local enable_seqscan = off;
select * from t1 where i not in (select j from t2);
set local enable_seqscan = on;

3. 更新统计信息 #

确保表的统计信息准确，让优化器能做出更合理的成本估算：

analyze t2;

4. 调整成本参数 #

如果数据库运行在SSD存储上，可降低random_page_cost（默认值为4），让优化器更倾向于选择索引扫描：

-- 临时设置，仅对当前会话有效
set local random_page_cost = 2;
-- 永久设置需修改postgresql.conf并重启数据库

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