PostgreSQL 9.6：含RETURNING的嵌套INSERT语句CTE性能过慢求优化

问题背景 #

有一张超5000万行的大表需转换格式，要求将插入table1后返回的rowid用于table2的插入操作。最初使用CTE实现，但性能极差：插入4800行耗时21秒、9600行耗时50秒，按此速度处理全量数据需约3天，远超出2小时的时间限制。

单独执行两个INSERT操作时性能提升100倍：无CTE的table1插入4800行仅需200毫秒、9600行500毫秒；table2插入耗时可忽略，完全能在规定时间内完成。

最初的CTE语句：

WITH inserted_data AS (
    INSERT INTO table1 (column1, column2, column3, column4)
    SELECT value1, value2, value3, value4
    FROM original_table
    RETURNING rowid, column4
)
INSERT INTO table2 (table1_id, column4)
SELECT rowid, column4
FROM inserted_data;

尝试嵌套CTE时出现语法错误：

INSERT INTO table2 (table1_id, column4)
SELECT rowid, column4
FROM (
    WITH inserted_data AS (
        INSERT INTO table1 (column1, column2, column3, column4)
        SELECT value1, value2, value3, value4
        FROM original_table
        RETURNING rowid, column4
    )
)

错误提示：

syntax error at or near "INTO"

可行改写方案 #

方案1：临时表存储中间结果 #

PostgreSQL的CTE是物化视图，会强制将中间结果写入磁盘，而临时表可通过内存配置优化性能，批量插入开销远低于CTE关联。

-- 创建临时表，存储插入table1后的rowid和column4
CREATE TEMP TABLE temp_inserted_data (
    rowid INT,
    column4 [替换为你的column4数据类型]
) ON COMMIT DROP; -- 事务结束自动清理

-- 第一步：批量插入table1，将返回结果写入临时表
INSERT INTO table1 (column1, column2, column3, column4)
SELECT value1, value2, value3, value4
FROM original_table
RETURNING rowid, column4 INTO TEMP temp_inserted_data;

-- 第二步：用临时表数据批量插入table2
INSERT INTO table2 (table1_id, column4)
SELECT rowid, column4
FROM temp_inserted_data;

优化配置：

• 临时调大temp_buffers参数（如SET temp_buffers = '1GB';），让临时表数据尽量留在内存，避免磁盘IO。
• 超大数据量可分批次处理（按original_table主键分段），减少单次资源占用。

方案2：利用原表关联获取rowid（需唯一关联字段） #

若column4（或其他组合字段）在original_table和table1中唯一，可先插入table1，再通过关联获取rowid插入table2：

-- 第一步：批量插入table1
INSERT INTO table1 (column1, column2, column3, column4)
SELECT value1, value2, value3, value4
FROM original_table;

-- 第二步：通过唯一字段关联插入table2
INSERT INTO table2 (table1_id, column4)
SELECT t1.rowid, ot.column4
FROM original_table ot
JOIN table1 t1 ON ot.column4 = t1.column4;

注意：若没有唯一字段，可在table1临时新增字段存储原表主键，关联完成后删除该字段。

方案3：COPY命令结合关联导入（极端大表场景） #

COPY命令比INSERT SELECT快数倍，适合5000万级数据：

-- 获取table1自增序列当前值（rowid为自增序列时）
SELECT currval('table1_rowid_seq') AS start_val;

-- 用COPY导入table1（需先将原表导出为CSV）
COPY table1 (column1, column2, column3, column4)
FROM '/path/to/original_data.csv' WITH (FORMAT csv, HEADER);

-- 计算导入的rowid范围
SELECT currval('table1_rowid_seq') AS end_val;

-- 通过原表与table1的唯一字段关联，插入table2
INSERT INTO table2 (table1_id, column4)
SELECT t1.rowid, ot.column4
FROM original_table ot
JOIN table1 t1 ON ot.column1 = t1.column1 AND ot.column2 = t1.column2;

通用性能优化建议 #

• 插入前关闭table1和table2的触发器、外键约束，完成后再开启，避免额外逻辑开销。
• 调大maintenance_work_mem和work_mem参数，增加内存分配，减少磁盘排序。
• 分批次插入（如每次100万行），降低锁竞争和资源占用。

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