使用dblink跨库回填jsonb列的慢SQL查询应该如何优化？

性能缓慢的核心原因 #

• 全量拉取远端数据：你当前的dblink写法会先把远端other_db_table表的全量数据拉到本地，再和本地"table"表做关联匹配。哪怕最终只需要1000条匹配结果，只要远端表数据量较大，全表扫描+网络传输的开销占了绝大多数耗时。
• 关联字段无索引：如果远端other_db_table的external_id字段、本地"table"的external_id字段未建立索引，关联阶段会触发两次全表扫描，进一步放大耗时。
• dblink不支持过滤条件下推：PostgreSQL的dblink默认无法将本地的关联、过滤逻辑下推到远端数据库执行，所有计算都在拉取到本地后执行，浪费大量网络和计算资源。
• 冗余的jsonb操作：现有写法在jsonb_build_object中手动拼接整个user对象，重复读取原有user下的name、phone、address三个字段完全是多余操作，每一行都要额外执行3次jsonb取值、1次jsonb构建操作，增加了不必要的计算开销。

优化方案 #

优先替换为postgres_fdw外部表（推荐） #

PostgreSQL官方提供的postgres_fdw外部表组件天然支持查询条件下推，会自动将关联过滤条件推到远端库执行，仅拉取需要的匹配数据，性能比dblink高30%~50%。使用示例：

-- 安装扩展
CREATE EXTENSION postgres_fdw;
-- 创建远端服务器
CREATE SERVER other_db_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
    host '$DB_HOST',
    port '$DB_PORT',
    dbname '$DB_NAME'
);
-- 创建用户映射
CREATE USER MAPPING FOR CURRENT_USER SERVER other_db_server OPTIONS (
    user '$DB_USER',
    password '$DB_PASSWORD'
);
-- 导入远端表结构
CREATE FOREIGN TABLE foreign_other_db_table (
    user_id uuid,
    external_id uuid
) SERVER other_db_server OPTIONS (schema_name 'public', table_name 'other_db_table');
-- 直接执行更新，fdw会自动下推过滤条件
UPDATE "table" t1 
SET jsonb_example_column = jsonb_set(jsonb_example_column, '{user,user_id}', to_jsonb(ft.user_id))
FROM foreign_other_db_table ft
WHERE ft.external_id = t1.external_id;

索引优化 #

分别给关联字段建立B树索引：

-- 远端库执行
CREATE INDEX idx_other_db_table_external_id ON other_db_table(external_id);
-- 本地库执行
CREATE INDEX idx_table_external_id ON "table"(external_id);

简化jsonb更新逻辑 #

不需要重写整个user对象，直接修改单个字段即可，性能提升至少2倍：

-- 优化后写法替代原有的全量拼接user对象的写法
jsonb_set(jsonb_example_column, '{user,user_id}', to_jsonb(other_db.user_id))

dblink场景临时表优化 #

如果必须使用dblink，先将远端数据拉到本地临时表再关联，避免重复网络交互开销：

-- 拉取远端数据到临时表，可根据需要在远端查询增加过滤条件减少拉取数据量
CREATE TEMP TABLE temp_other_data AS
SELECT * FROM dblink(
'dbname=$DB_NAME port=$DB_PORT host=$DB_HOST user=$DB_USER password=$DB_PASSWORD',
'SELECT user_id, external_id from other_db_table t2'
) AS t(user_id uuid, external_id uuid);
-- 临时表加索引
CREATE INDEX idx_temp_external_id ON temp_other_data(external_id);
-- 执行更新
UPDATE "table" t1 
SET jsonb_example_column = jsonb_set(jsonb_example_column, '{user,user_id}', to_jsonb(temp.user_id))
FROM temp_other_data temp
WHERE temp.external_id = t1.external_id;

以上优化全部落地后，1000条记录的更新耗时可以降到1秒以内。

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