如何提升PostgreSQL查询效率？大表关联查询优化问询

原查询 #

SELECT DISTINCT z.id as d_id, z.pcd as d_pcd, z.forename as d_forename, z.surname as d_surname, z.first as d_first, z.last as d_last, z.eastings as d_eastings, z.northings as d_northings, z.rn as d_rn
FROM table1 z 
INNER JOIN (
    SELECT DISTINCT a.id, b.first
    FROM table1 a
    INNER JOIN (SELECT DISTINCT fullname, first FROM MatView1) b
    ON (a.fullname = b.fullname)) x
ON z.id = x.id
WHERE z.last <= (x.first+1) and z.last >= (x.first-3);

环境信息 #

• table1：1.45亿行、30列；id、fullname、first、last字段已建单字段索引；id/fullname为文本类型，first/last为整数年份；因需保留重复数据无法设主键。
• MatView1：物化视图，含约20万个可能重复的fullname，作为筛选基础。

执行计划核心问题 #

从EXPLAIN (ANALYZE, BUFFERS)结果来看，主要性能瓶颈：

1. 对table1进行多次全表扫描（Parallel Seq Scan），未有效利用索引。
2. 大量磁盘排序（external merge），IO开销巨大，排序耗时占比极高。
3. 多层DISTINCT导致重复去重和排序，浪费计算资源。
4. Merge Join依赖全表排序后的z.id，进一步放大排序开销。
5. 哈希表批次数过多（Batches: 8192），说明内存不足，大量数据需磁盘交换。

优化方案 #

1. 简化查询结构，去除冗余DISTINCT #

原查询中内层SELECT DISTINCT a.id, b.first和外层DISTINCT存在重复去重逻辑，可调整为仅保留外层去重，或通过提前过滤减少去重数据量：

SELECT DISTINCT z.id as d_id, z.pcd as d_pcd, z.forename as d_forename, z.surname as d_surname, z.first as d_first, z.last as d_last, z.eastings as d_eastings, z.northings as d_northings, z.rn as d_rn
FROM table1 z 
INNER JOIN (
    SELECT a.id, b.first
    FROM table1 a
    INNER JOIN (SELECT DISTINCT fullname, first FROM MatView1) b
    ON a.fullname = b.fullname
    -- 提前过滤存在符合年份条件的id，减少后续JOIN数据量
    WHERE EXISTS (
        SELECT 1 FROM table1 z_sub 
        WHERE z_sub.id = a.id 
        AND z_sub.last BETWEEN (b.first - 3) AND (b.first + 1)
    )
) x ON z.id = x.id
WHERE z.last BETWEEN (x.first - 3) AND (x.first + 1);

2. 创建复合/覆盖索引，避免全表扫描 #

单字段索引无法覆盖查询的JOIN和过滤需求，需创建针对性复合索引：

• 加速table1与MatView1的JOIN：

  CREATE INDEX idx_table1_fullname_id ON table1 (fullname, id);
  

• 加速table1的id匹配和年份过滤，同时覆盖查询所需字段（避免回表）：

  CREATE INDEX idx_table1_id_last_cover ON table1 (id, last) 
  INCLUDE (pcd, forename, surname, first, eastings, northings, rn);
  

• 优化MatView1的DISTINCT和JOIN：

  CREATE INDEX idx_matview1_fullname_first ON matview1 (fullname, first);
  

3. 调整查询逻辑，提前过滤数据 #

将年份过滤逻辑嵌入子查询，提前筛除不符合条件的数据，减少后续JOIN和排序的数据量：

SELECT DISTINCT z.id as d_id, z.pcd as d_pcd, z.forename as d_forename, z.surname as d_surname, z.first as d_first, z.last as d_last, z.eastings as d_eastings, z.northings as d_northings, z.rn as d_rn
FROM table1 z
INNER JOIN (
    SELECT a.id, b.first
    FROM table1 a
    INNER JOIN (SELECT DISTINCT fullname, first FROM MatView1) b ON a.fullname = b.fullname
    -- 子查询阶段就过滤掉无符合年份记录的id
    WHERE EXISTS (
        SELECT 1 FROM table1 z_sub 
        WHERE z_sub.id = a.id 
        AND z_sub.last BETWEEN (b.first - 3) AND (b.first + 1)
    )
) x ON z.id = x.id
WHERE z.last BETWEEN (x.first - 3) AND (x.first + 1);

4. 优化内存配置，减少磁盘排序 #

多次磁盘排序说明work_mem不足，临时调高会话级内存配置（根据服务器内存调整，如128MB/256MB）：

SET work_mem = '64MB';

若该查询频繁执行，可在postgresql.conf中调整全局work_mem（需重启生效），同时可适当调高maintenance_work_mem辅助索引创建。

5. 强制使用Hash Join，替换Merge Join #

Merge Join依赖全表排序，开销极大，可临时关闭Merge Join，让PostgreSQL选择更高效的Hash Join：

SET enable_mergejoin = off;

也可通过调整查询结构，让子查询x（结果集远小于table1）作为驱动表，自然触发Hash Join。

6. 预计算物化视图，减少重复计算 #

若该查询频繁执行，可创建物化视图预计算筛选后的id集合，避免每次重复计算：

CREATE MATERIALIZED VIEW mv_filtered_ids AS
SELECT DISTINCT a.id, b.first
FROM table1 a
INNER JOIN (SELECT DISTINCT fullname, first FROM MatView1) b ON a.fullname = b.fullname
WHERE EXISTS (
    SELECT 1 FROM table1 z_sub 
    WHERE z_sub.id = a.id 
    AND z_sub.last BETWEEN (b.first - 3) AND (b.first + 1)
);
-- 创建索引加速后续查询
CREATE INDEX idx_mv_filtered_ids_id_first ON mv_filtered_ids (id, first);

后续查询直接使用物化视图：

SELECT DISTINCT z.id as d_id, z.pcd as d_pcd, z.forename as d_forename, z.surname as d_surname, z.first as d_first, z.last as d_last, z.eastings as d_eastings, z.northings as d_northings, z.rn as d_rn
FROM table1 z
INNER JOIN mv_filtered_ids x ON z.id = x.id
WHERE z.last BETWEEN (x.first - 3) AND (x.first + 1);

注意定期通过REFRESH MATERIALIZED VIEW mv_filtered_ids;刷新数据。

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