PostgreSQL13 FDW关联本地与外部表带ORDER BY和JSONB查询慢如何优化

问题根因 #

从你提供的执行计划可以看到，耗时最高的环节是排序步骤触发了磁盘溢出：Sort Method: external merge Disk: 82752kB。
原查询逻辑是先完成两张表的全量关联，把体积较大的JSONB字段settings也全部加载后再做排序，排序时需要携带大量大体积字段，总数据量超出了内存排序阈值，只能走性能极差的磁盘排序，最终导致查询耗时达到14秒。

优化方案 #

方案1：调整查询逻辑，先排序取数再关联外部表（推荐，适配动态排序需求） #

核心思路是完全避开大字段参与排序环节：先在本地debtors表完成排序、取前1000条记录（仅需要处理id、name、registry_uuid三个小字段，排序速度极快），再用这1000条的关联键去外部表拉取对应settings字段即可。
改造后的SQL如下：

SELECT 
    d.id,
    d.name,
    dr.settings
FROM (
    -- 先在本地表完成排序、分页，仅涉及小字段，排序效率极高
    SELECT id, name, registry_uuid
    FROM debtors
    ORDER BY name 
    LIMIT 1000 OFFSET 0
) d
INNER JOIN debtor_registry dr ON dr.id = d.registry_uuid;

这个方案完全适配你动态排序的需求，不管前端传递什么排序字段，都可以直接放在子查询的ORDER BY子句中，不需要改动其他逻辑。

方案2：调大work_mem参数，避免排序溢出到磁盘 #

如果不想修改查询逻辑，可以临时调大当前会话的排序内存阈值，让排序操作在内存中完成，性能会有明显提升：

-- 执行查询前先运行，设置当前会话的排序内存上限为128MB，足够覆盖当前80MB+的排序需求
SET work_mem = '128MB';
-- 再执行原查询即可

如果需要全局生效，可以修改postgresql.conf配置文件中的work_mem参数，注意全局配置不要设置过大，避免多并发场景下内存占用过高。

额外优化建议 #

给外部表对应的远程源表company_debtor的id字段建立主键/普通索引，可进一步加快关联查询的速度。

内容的提问来源于stack exchange，提问作者Shift 'n Tab