批量向新表插入数据过慢，求优化方案及索引性能分析

我有两张存储用户信息的表：表A约700万行数据，表B初始无数据，两张表均在user_id字段上建有索引。需求是将表A中部分已有字段的数据插入表B，但批量执行过程中性能远超预期的慢。

当前使用的SQL代码：

with toInsert as (
    select a.user_id, a.created_at, a.registered_date
    from a
    left outer join b using (user_id)
    where b.user_id is null
    limit 50
)
insert into b (user_id, created_at, registered_date)
select user_id, created_at, registered_date
from toInsert
returning *;

随着表B数据量增长，该语句执行一次约需3分钟。查询计划显示对两张表执行索引扫描后进行Merge Join操作，具体计划如下：

Insert on b  (cost=0.86..53.58 rows=50 width=48)
 ->  Subquery Scan on toinsert  (cost=0.86..53.58 rows=50 width=48)
    ->  Limit  (cost=0.86..53.33 rows=50 width=32)
      ->  Merge Anti Join  (cost=0.86..6222611.33 rows=5929090 width=32)
            Merge Cond: (a.user_id = b_1.user_id)
            ->  Index Scan using a_pkey on a  (cost=0.43..6189771.44 rows=6460394 width=32)
            ->  Index Only Scan using b_pkey on b b_1  (cost=0.42..10047.61 rows=531304 width=16)

疑问：

1. 如何加速该查询？
2. 为何索引表现如此糟糕？
3. 尝试过用where not in替代左连接，速度略有提升，但每次批量执行仍会全表扫描。

一、索引表现差的核心原因 #

从查询计划来看，虽然用到了索引，但Merge Anti Join需要遍历表A的全部索引，同时与表B的索引做全量合并对比，才能筛选出未存在于B中的user_id。随着B的数据量增长，合并对比的开销线性上升，再加上你每次只取50条数据，数据库不得不扫描大量无关数据才能找到符合条件的少量结果，直接导致单次执行耗时剧增。

where not in的版本略快，但本质还是要做全量存在性检查，同样无法避免遍历表A的大部分数据，性能瓶颈没有根本解决。

二、具体优化方法 #

1. 按user_id范围分批处理，彻底避免全表扫描 #

放弃随机筛选50条的方式，利用user_id的有序性分片处理。每次只处理表A中大于表B当前最大user_id的区间，通过索引快速定位目标数据：

WITH max_b_user AS (
    -- 初始时B为空，COALESCE返回0或user_id的最小值
    SELECT COALESCE(MAX(user_id), 0) AS max_id FROM b
)
INSERT INTO b (user_id, created_at, registered_date)
SELECT a.user_id, a.created_at, a.registered_date
FROM a, max_b_user
WHERE a.user_id > max_b_user.max_id
ORDER BY a.user_id
LIMIT 500; -- 可根据数据库性能调整批次大小

这种方式直接利用索引定位范围，无需全表扫描和Merge Join，性能会随批次稳定，不会随B的数据量增长而大幅下降。

2. 调大批次大小 #

当前每次仅插入50条，执行次数过多，连接、计划解析的固定开销累加后非常可观。建议将批次调到500-1000条（具体数值根据服务器负载调整），减少总执行次数，提升整体迁移效率。

3. 用NOT EXISTS替代左连接 #

NOT EXISTS在PostgreSQL中针对存在性检查的执行计划通常更高效，尤其是结合范围过滤时：

INSERT INTO b (user_id, created_at, registered_date)
SELECT a.user_id, a.created_at, a.registered_date
FROM a
WHERE NOT EXISTS (
    SELECT 1 FROM b WHERE b.user_id = a.user_id
)
ORDER BY a.user_id
LIMIT 500;

如果搭配前面的范围分片逻辑，能进一步缩小扫描范围。

4. 更新表B的统计信息 #

定期执行ANALYZE b;，让数据库获取最新的表数据分布统计，确保查询优化器能生成更准确的执行计划，避免错误的索引选择。

5. 全量迁移（业务允许的情况下） #

如果不需要分批插入，直接执行全量插入会比分批快得多，数据库会采用更高效的批量处理逻辑：

INSERT INTO b (user_id, created_at, registered_date)
SELECT a.user_id, a.created_at, a.registered_date
FROM a
LEFT JOIN b USING (user_id)
WHERE b.user_id IS NULL;

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