PostgreSQL大表JOIN过慢问题排查与优化咨询

首先来拆解你的问题，核心是大表JOIN效率极低，同时想知道UPDATE是否是更优方案，我们一步步来看：

为什么当前的CREATE TABLE AS这么慢？ #

从你的执行计划可以看到几个关键的性能瓶颈：

1. 巨量的排序去重开销：执行计划里的Unique操作依赖Sort，成本高达233亿，这是因为DISTINCT需要把所有JOIN结果（预估7200万行）全量排序后去重，磁盘排序的话速度会极慢，尤其是如果你的work_mem设置偏小，会频繁刷磁盘。
2. 统计信息严重失真：执行计划里t2的Foreign Scan预估行数只有278行，但实际是1亿行，这说明PostgreSQL对外部表（patstat2.tls211_pat_publn应该是FDW外部表）的统计信息不准确，导致优化器做出了不合理的执行计划选择（比如用Hash Join，但实际t2数据量极大，Hash的内存开销会非常高）。
3. JOIN后行数爆炸：因为t1的appln_id不是唯一键，t2的appln_id也不是唯一键（主键是pat_publn_id），所以JOIN会产生大量笛卡尔积行，直接导致后续排序去重的压力倍增。

UPDATE是否可行/更快？ #

这完全取决于你的业务需求：

• 如果你的目标是每个t1行只对应一组publn_nr和publn_nr_original（比如每个appln_id取最新发布的记录，或者任意一条），那UPDATE绝对是更快的方案——因为不需要处理JOIN后的大量重复行，也不需要排序去重。
• 如果你的目标是保留所有t1和t2匹配的组合（即一对多的所有结果行），那UPDATE不可行，因为UPDATE只能给每行设置一个值，无法保留多组匹配结果。

适合用UPDATE的参考步骤： #

1. 先给t1添加需要的列：

ALTER TABLE addresses_googleresponse 
ADD COLUMN publn_nr varchar(15),
ADD COLUMN publn_nr_original varchar(100);

2. 对t2先做聚合（避免同一appln_id多次更新同一t1行），再执行UPDATE：

UPDATE addresses_googleresponse t1
SET publn_nr = t2.publn_nr, 
    publn_nr_original = t2.publn_nr_original
FROM (
    SELECT appln_id, publn_nr, publn_nr_original
    FROM (
        SELECT 
            appln_id, 
            publn_nr, 
            publn_nr_original,
            -- 按发布日期取最新的一条，你可以根据需求调整排序规则
            ROW_NUMBER() OVER (PARTITION BY appln_id ORDER BY publn_date DESC) AS rn
        FROM patstat2.tls211_pat_publn
    ) sub
    WHERE rn = 1
) t2
WHERE t1.appln_id = t2.appln_id;

这个方案的核心是先把t2中每个appln_id的记录聚合为一条，再和t1关联更新，避免了大量重复操作，速度会比原来的CREATE TABLE AS快很多。

如果你需要保留所有JOIN组合（必须用CREATE TABLE AS），优化建议： #

1. 去掉不必要的DISTINCT，提前去重：
   如果DISTINCT是因为JOIN产生了完全重复的行，那可以先在t1或t2中提前去重，再JOIN：

CREATE TABLE new AS
SELECT t1.*, t2.publn_nr, t2.publn_nr_original
FROM (
    SELECT DISTINCT * FROM addresses_googleresponse
) t1
JOIN (
    SELECT DISTINCT appln_id, publn_nr, publn_nr_original FROM patstat2.tls211_pat_publn
) t2 ON t1.appln_id = t2.appln_id;

2. 修正统计信息：
   对两个表执行ANALYZE，让优化器得到准确的数据量估计：

ANALYZE addresses_googleresponse;
-- 如果是外部表，postgres_fdw可以直接执行ANALYZE更新统计信息
ANALYZE patstat2.tls211_pat_publn;

3. 临时增大work_mem：
   排序操作需要足够的内存，临时调大work_mem可以让排序在内存中完成，避免磁盘IO：

SET work_mem = '64MB'; -- 根据你的服务器内存调整，比如128MB甚至256MB

4. 分批处理：
   把数据按appln_id的范围拆分，分批JOIN插入，避免一次性处理全量数据：

-- 先创建空表
CREATE TABLE new AS SELECT * FROM addresses_googleresponse LIMIT 0;
ALTER TABLE new ADD COLUMN publn_nr varchar(15), ADD COLUMN publn_nr_original varchar(100);

-- 分批插入，比如按appln_id每10万一批
INSERT INTO new
SELECT t1.*, t2.publn_nr, t2.publn_nr_original
FROM addresses_googleresponse t1
JOIN patstat2.tls211_pat_publn t2 ON t1.appln_id = t2.appln_id
WHERE t1.appln_id BETWEEN 1 AND 100000;

-- 重复执行上述INSERT，调整范围直到完成

5. 尝试Merge Join：
   如果两个表的appln_id都有索引，且数据是有序的，Merge Join会比Hash Join更适合大表，你可以临时关闭Hash Join测试：

SET enable_hashjoin = off;
CREATE TABLE new AS SELECT DISTINCT t1.*, t2.publn_nr, t2.publn_nr_original FROM addresses_googleresponse t1 JOIN patstat2.tls211_pat_publn t2 ON t1.appln_id = t2.appln_id;
SET enable_hashjoin = on;

额外注意点： #

• 你的PostgreSQL版本是9.6.5，比较老旧，新版本（比如12+）在大表JOIN和排序上有很多性能优化，如果可能的话升级会有明显帮助。
• 如果t2是外部表，检查FDW的fetch_size参数，调大它可以减少网络交互次数，提升外部表的读取速度。

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