如何在PostgreSQL中从CTE筛选未更新记录优化插入操作？

在PostgreSQL中，你可以直接利用CTE的特性，通过差集运算或者左连接筛选空值来高效获取temp中未被更新的记录，避免额外子查询开销，实现成本最优的插入。

方法一：使用EXCEPT获取差集（简洁高效） #

利用temp和updated的记录差集，直接得到需要插入的数据：

with temp(id, firstname, lastname) as (
  Select * 
  from (values(1, 'albert','besra'),
              (5, 'abc', 'def')
       ) as a(id, firstname, lastname)
),
updated as( 
  update scientist s 
    set lastname = t.lastname 
  from temp t 
  where s.id = t.id returning t.*
)
insert into scientist(id, firstname, lastname)
select id, firstname, lastname
from temp
except
select id, firstname, lastname
from updated;

方法二：左连接筛选未匹配记录（适合复杂场景） #

通过左连接temp和updated，筛选出updated中无匹配的记录，且仅返回id字段用于对比，进一步降低开销：

with temp(id, firstname, lastname) as (
  Select * 
  from (values(1, 'albert','besra'),
              (5, 'abc', 'def')
       ) as a(id, firstname, lastname)
),
updated as( 
  update scientist s 
    set lastname = t.lastname 
  from temp t 
  where s.id = t.id returning t.id
)
insert into scientist(id, firstname, lastname)
select t.id, t.firstname, t.lastname
from temp t
left join updated u on t.id = u.id
where u.id is null;

优势说明 #

• 避免了NOT EXISTS子查询可能带来的额外索引扫描，直接复用CTE中已生成的updated数据集
• 针对分区表，PostgreSQL能更好地优化这类集合运算的执行计划，减少跨分区的不必要扫描
• 方法二中仅返回id字段用于对比，进一步降低数据传输和计算的成本

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