多嵌套SELECT+UNION ALL的INSERT语句性能优化求助

首先得揪出当前写法的性能核心瓶颈：你现在的INSERT语句里，每个单元格都是独立的SELECT ... WHERE combi_id = X子查询，这意味着要执行 160个combi_id × 160个表 = 25600次索引扫描，而且SQL语句过于冗长，导致PostgreSQL的查询规划器花了3秒多来解析它——这完全是没必要的开销！

下面是几个能快速把性能拉到1秒以内的优化方案，按优先级排序：

1. 用JOIN替代嵌套子查询（核心优化） #

本质上，你要做的是把160个fw_n表按combi_id做横向拼接，然后批量插入到fw_final。用JOIN的话，每个表只需要扫描一次，而不是几百次。

示例SQL： #

INSERT INTO fw_final (combos1_1, combos2_2, /* ... 直到 combos160_160 ... */)
SELECT
  fw_1.combos_1,
  fw_2.combos_1,
  /* ... 依次列出 fw_3.combos_1 到 fw_160.combos_1 ... */
  fw_160.combos_1
FROM fw_1
JOIN fw_2 ON fw_1.combi_id = fw_2.combi_id
JOIN fw_3 ON fw_1.combi_id = fw_3.combi_id
/* ... 依次JOIN fw_4到fw_160 ... */
JOIN fw_160 ON fw_1.combi_id = fw_160.combi_id
ORDER BY fw_1.combi_id;

这个写法会让PostgreSQL一次性把所有表的数据关联起来，每个表只做一次扫描（因为combi_id是主键，会走索引扫描），总扫描次数从25600降到160，查询规划时间也会从3秒直接降到几十毫秒。

2. 用动态SQL自动生成JOIN语句（避免手动写160次） #

手动写160个JOIN和SELECT列太麻烦还容易出错，用PL/pgSQL动态生成SQL是更高效的方式：

DO $$
DECLARE
  i integer;
  select_cols text := '';
  join_clauses text := 'FROM fw_1';
  target_cols text;
BEGIN
  -- 生成目标列列表（combos1_1到combos160_160）
  SELECT string_agg('combos' || num || '_' || num, ', ')
  INTO target_cols
  FROM generate_series(1, 160) AS num;

  -- 生成SELECT子句和JOIN子句
  FOR i IN 2..160 LOOP
    select_cols := select_cols || ', fw_' || i || '.combos_1';
    join_clauses := join_clauses || ' JOIN fw_' || i || ' ON fw_1.combi_id = fw_' || i || '.combi_id';
  END LOOP;

  -- 执行动态生成的INSERT语句
  EXECUTE format(
    'INSERT INTO fw_final (%s) SELECT fw_1.combos_1 %s %s ORDER BY fw_1.combi_id',
    target_cols,
    select_cols,
    join_clauses
  );
END $$;

这段代码会自动帮你生成完整的INSERT语句，不管你有多少个fw_n表，改一下generate_series的范围就行。

3. 配合COPY进一步提升插入速度 #

如果上面的JOIN查询已经很快，但插入还想再提速，可以用COPY FROM直接导入查询结果——COPY的批量插入性能比INSERT高很多，尤其是大数据量场景：

COPY fw_final (combos1_1, combos2_2, /* ... 直到 combos160_160 ... */)
FROM (
  SELECT
    fw_1.combos_1,
    fw_2.combos_1,
    /* ... 依次列出 fw_3.combos_1 到 fw_160.combos_1 ... */
    fw_160.combos_1
  FROM fw_1
  JOIN fw_2 ON fw_1.combi_id = fw_2.combi_id
  /* ... 依次JOIN fw_4到fw_160 ... */
  JOIN fw_160 ON fw_1.combi_id = fw_160.combi_id
) TO STDIN WITH (FORMAT binary);

不过注意：COPY需要你有权限操作，而且如果fw_final有约束/索引，插入前先临时移除会更快（见下面的环境优化）。

4. 数据库环境层面的辅助优化 #

临时移除fw_final的主键/索引 #

插入数据时维护索引会消耗大量IO，尤其是传统HDD（你用的是5400/7200转的盘，IO是瓶颈）。可以先删除主键，插入完成后再重建：

-- 先删除主键
ALTER TABLE fw_final DROP CONSTRAINT fw_final_pkey;

-- 执行INSERT/COPY操作

-- 重建主键（比边插边维护快N倍）
ALTER TABLE fw_final ADD CONSTRAINT fw_final_pkey PRIMARY KEY (combi_id);

调整PostgreSQL配置参数 #

针对你的16GB内存和8核CPU，可以临时调整以下参数（重启后失效，要永久改的话编辑postgresql.conf）：

• work_mem = 128MB：给JOIN/排序分配更多内存，避免磁盘临时文件
• maintenance_work_mem = 2GB：重建索引时用更多内存，加速操作
• shared_buffers = 4GB：让PostgreSQL缓存更多数据到内存

保持UNLOGGED表的使用 #

你已经用了UNLOGGED表，这个非常好——UNLOGGED表不写入WAL日志，插入速度比普通表快2-3倍，只要你能接受数据库崩溃时UNLOGGED表的数据丢失（从你的场景看应该没问题）。

为什么原来的写法这么慢？ #

从你的执行计划能看出来：

• 规划时间3.2秒：PostgreSQL要解析几百个嵌套子查询，生成执行计划的成本极高
• 执行时间4秒：25600次索引扫描，每次虽然快，但累计起来就是巨大的IO开销（HDD的随机IO本来就慢）

用JOIN的话，这些问题都能解决，执行时间应该能降到几百毫秒以内，完全满足你1秒的要求。

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