为何UNIQUE约束方案比NOT EXISTS插入慢？PostgreSQL性能疑问

需要将表insert_values（与insert_base均包含a、b属性）的数据去重插入insert_base，要求既避免插入insert_base已有的元组，也排除insert_values内的重复元组。尝试了两种方案：

第一种方案：

INSERT INTO insert_base SELECT DISTINCT * FROM insert_values IV
WHERE NOT EXISTS
(SELECT * FROM insert_base IB WHERE IV.a = IB.a AND IV.b = IB.b);

第二种方案：先给insert_base(a,b)添加UNIQUE约束，再执行：

INSERT INTO insert_base SELECT * FROM insert_values ON CONFLICT DO NOTHING;

实际执行时第一种方案速度显著更快（500ms vs 1300ms），对此存在疑问：

1. 两种方案功能是否大致相同？
2. UNIQUE约束会创建索引，为何第二种方法反而更慢？
3. 给第一种方案的insert_base(a,b)添加索引后，运行速度反而比无索引时略慢？
   使用PostgreSQL数据库，恳请解惑。

1. 两种方案的功能差异 #

功能上确实基本等价，都能实现目标，但底层执行逻辑完全不同：

• 第一种方案：先对insert_values做DISTINCT去重，再通过NOT EXISTS过滤掉insert_base已存在的记录，最后批量插入剩余数据。整个过程是先过滤后插入，插入时数据库无需额外做冲突检查。
• 第二种方案：直接将insert_values的所有数据（包含重复）尝试插入，依赖UNIQUE约束的冲突检测机制，遇到已存在的记录（不管是insert_base原有还是本次插入的重复）就跳过。这个过程是先插入再跳过冲突，数据库要为每一条待插入记录做唯一性校验。

2. 第二种方案更慢的原因 #

虽然UNIQUE约束会创建索引，但ON CONFLICT DO NOTHING的执行逻辑导致了额外开销：

• 当insert_values中有大量重复记录时，数据库会对每一条记录都执行索引查找判断是否冲突，包括insert_values内部的重复项——而第一种方案的DISTINCT在扫描insert_values时就已经把内部重复过滤掉了，减少了后续需要检查的记录数。
• ON CONFLICT的冲突检测是在插入阶段逐行触发的，而NOT EXISTS的过滤是在查询阶段批量完成的，PostgreSQL对批量过滤的优化通常比逐行冲突检查更高效。

3. 给第一种方案加索引后变慢的原因 #

第一种方案的NOT EXISTS子查询，在没有索引时，PostgreSQL可能会选择哈希连接（Hash Join）：将insert_base的(a,b)数据加载到哈希表中，然后和去重后的insert_values数据做哈希匹配，这种方式在表数据量较大时，比索引查找的开销更低。
当添加了索引后，优化器可能会选择嵌套循环连接（Nested Loop），用insert_values的每条记录去索引中查找是否存在匹配。如果insert_values去重后的记录数很多，嵌套循环的逐行索引查找会比哈希连接的批量匹配更慢，导致整体执行时间增加。

如果想验证这个猜想，可以用EXPLAIN ANALYZE查看两种情况下的执行计划，对比连接类型和耗时分布。

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