含DISTINCT ON子查询的PostgreSQL慢查询优化求助

场景与需求 #

• file_group表约5000万条记录，file表约6000万条；多数file_group关联1个file，部分无关联或关联多个
• 插入操作频繁，需实时获取最新数据，无法使用物化视图
• 核心需求：按partition_1, partition_2, partition_3, partition_4组合分组，取每组中creation_time最新的file_group，再左关联对应的file，结果按指定顺序排序

表结构 #

简化结构 #

CREATE TABLE file_group (
  id serial primary key,
  tag varchar(64),
  creation_time timestamp
);

CREATE TABLE file (
  id serial primary key,
  file_group_id integer not null references file_group,
  name text,
  ordering int
);

实际结构 #

file_group表包含多个分区列（partition_1至partition_4），原存储于HSTORE列；file表包含name, creation_time, metadata等业务字段。

现有查询与瓶颈 #

完整查询语句 #

select file.name, file.creation_time, file.metadata, fg.partition_1, fg.partition_2, fg.partition_3, fg.partition_4
from (
    select
        distinct on (file_group.partition_1, file_group.partition_2, file_group.partition_3, file_group.partition_4)
        file_group.id, file_group.partition_1, file_group.partition_2, file_group.partition_3, file_group.partition_4
    from file_group
    where (
        file_group.dataset_id = 123
        and file_group.partition_2 > '2024-03-15'
        and file_group.partition_2 < '2024-04-15'
    )
    order by
        file_group.partition_1,
        file_group.partition_2,
        file_group.partition_3,
        file_group.partition_4,
        file_group.creation_time desc
) as fg
left join file on fg.id = file.file_group_id
order by
    fg.partition_1,
    fg.partition_2,
    fg.partition_3,
    fg.partition_4,
    file.ordering,
    file.creation_time;

现有索引 #

create index file_group_full_nulls_last
    on file_group (dataset_id asc, partition_1 asc, partition_2 asc, partition_3 asc, partition_4 asc,
                        creation_time desc);

性能瓶颈 #

• 当匹配的file_group结果集较大时，查询优化器选择嵌套循环连接，需循环执行16万+次file表索引扫描，尽管单次扫描快，但累积开销显著
• 尝试切换到哈希/合并连接时，执行时间反而大幅增加（从887ms增至9561ms），主要因合并连接需全量扫描file表并排序

优化方案 #

1. 构建file表的覆盖索引 #

现有file_file_group_id索引仅包含file_group_id，关联时需回表读取业务字段。创建覆盖索引避免回表：

CREATE INDEX idx_file_group_id_covering ON file (file_group_id)
INCLUDE (name, creation_time, metadata, ordering);

此索引可让嵌套循环连接时直接从索引获取所需所有字段，减少磁盘IO开销。

2. 优化file_group表的覆盖索引 #

现有索引已匹配DISTINCT ON的排序逻辑，但可将子查询所需的id字段加入INCLUDE，让子查询完全走索引扫描，无需访问表数据：

CREATE INDEX idx_file_group_partition_covering ON file_group 
(dataset_id asc, partition_1 asc, partition_2 asc, partition_3 asc, partition_4 asc, creation_time desc)
INCLUDE (id);

替换原索引或保留原索引（视查询多样性而定），此优化可降低子查询的执行时间。

3. 用窗口函数替代DISTINCT ON #

尝试用ROW_NUMBER()窗口函数实现相同逻辑，优化器可能生成更优的执行计划：

WITH ranked_fg AS (
    SELECT 
        id, partition_1, partition_2, partition_3, partition_4,
        ROW_NUMBER() OVER (
            PARTITION BY partition_1, partition_2, partition_3, partition_4 
            ORDER BY creation_time DESC
        ) AS rn
    FROM file_group
    WHERE dataset_id = 123 
      AND partition_2 > '2024-03-15' 
      AND partition_2 < '2024-04-15'
)
SELECT 
    f.name, f.creation_time, f.metadata,
    rf.partition_1, rf.partition_2, rf.partition_3, rf.partition_4
FROM ranked_fg rf
LEFT JOIN file f ON rf.id = f.file_group_id
WHERE rn = 1
ORDER BY 
    rf.partition_1, rf.partition_2, rf.partition_3, rf.partition_4,
    f.ordering, f.creation_time;

结合覆盖索引，窗口函数方案可避免DISTINCT ON的Unique操作，直接过滤出最新的file_group。

4. 引导优化器选择哈希连接（按需） #

若嵌套循环仍无改善，可临时禁用嵌套循环测试哈希连接性能：

-- 临时禁用嵌套循环
SET enable_nestloop = OFF;
-- 执行查询
-- ...
-- 恢复默认设置
SET enable_nestloop = ON;

若哈希连接性能更优，可调整effective_cache_size等参数让优化器自动选择，或针对此查询设置enable_nestloop = OFF。

5. 分区表优化（长期方案） #

若partition_2为日期类型，可将file_group表按partition_2分区，让WHERE条件直接扫描对应分区，减少扫描数据量：

-- 示例：按月份分区
CREATE TABLE file_group_partitioned (
    id serial primary key,
    dataset_id int,
    partition_1 varchar(64),
    partition_2 varchar(64),
    partition_3 varchar(64),
    partition_4 varchar(64),
    creation_time timestamp
) PARTITION BY RANGE (partition_2::date);

-- 创建对应分区
CREATE TABLE file_group_202403 PARTITION OF file_group_partitioned
FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');

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