如何优化PostgreSQL中耗时12分钟的每日查询的连接与排序

表结构定义 #

CREATE TABLE metric_events (
    id uuid PRIMARY KEY,
    metric_id integer NOT NULL,
    event_at timestamp with time zone NOT NULL,
    code text NOT NULL, 
    test boolean DEFAULT false NOT NULL,
    success boolean DEFAULT false NOT NULL
);

CREATE TABLE hourly_metric_event_counters (
    start_at timestamp with time zone NOT NULL,
    metric_id integer NOT NULL,
    code text NOT NULL,
    test boolean NOT NULL,
    total_count integer DEFAULT 0,
    success_count integer DEFAULT 0,
    PRIMARY KEY (metric_id, test, code, start_at)
);

-- 现有索引
CREATE UNIQUE INDEX metric_events_uuid_index ON metric_events(id);
CREATE INDEX metric_events_event_at_index on metric_events(event_at);
CREATE INDEX metric_events_metric_id_and_event_at_index ON metric_events(metric_id, event_at);
CREATE INDEX hourly_metric_event_counters_start_at_index ON hourly_metric_event_counters(start_at);

待优化的每日查询 #

SELECT date_trunc('hour', b.event_at),
       b.metric_id,
       b.code,
       b.test,
       COUNT(b.*) - MAX(bec.total_count) as total_diff,
       SUM(CASE WHEN b.success THEN 1 ELSE 0 END) - MAX(bec.success_count) as success_diff
FROM metric_events b
JOIN hourly_metric_event_counters bec
ON bec.metric_id = b.metric_id AND
   bec.test = b.test AND
   bec.code = b.code AND
   bec.start_at = date_trunc('hour', b.event_at)
WHERE event_at >= date_trunc('hour', NOW() - interval '24 hours') AND event_at < date_trunc('hour', NOW() - interval '1 hour')
GROUP BY 1, 2, 3, 4
HAVING COUNT(b.*) - MAX(bec.total_count) != 0 OR
       SUM(CASE WHEN b.success THEN 1 ELSE 0 END) - MAX(bec.success_count) != 0

现状与统计数据 #

• metric_events总记录数：246,394,965；最近24小时记录数：46,524,166
• hourly_metric_event_counters总记录数：70,559,661；最近24小时记录数：26,054
• 数据库版本：PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by x86_64-pc-linux-gnu-gcc (GCC) 7.4.0, 64-bit
• 当前查询耗时约12分钟，关闭enable_parallel_append后性能有提升但未达预期

优化方案 #

1. 为metric_events添加覆盖索引 #

当前查询需按event_at过滤，同时分组、聚合和关联依赖metric_id、code、test、success字段，建议创建覆盖索引避免回表：

CREATE INDEX idx_metric_events_event_at_covering ON metric_events(event_at)
INCLUDE (metric_id, code, test, success);

或创建带分组字段前缀的组合索引，利用索引排序减少分组阶段的排序开销：

CREATE INDEX idx_metric_events_event_at_group ON metric_events(event_at, metric_id, code, test)
INCLUDE (success);

2. 重构查询逻辑，减少数据扫描量 #

由于hourly_metric_event_counters最近24小时仅2万多条数据，可先过滤目标计数器数据，再关联metric_events做聚合，避免一次性扫描4600万条记录：

WITH target_counters AS (
    SELECT metric_id, test, code, start_at, total_count, success_count
    FROM hourly_metric_event_counters
    WHERE start_at >= date_trunc('hour', NOW() - interval '24 hours') 
      AND start_at < date_trunc('hour', NOW() - interval '1 hour')
)
SELECT 
    tc.start_at,
    tc.metric_id,
    tc.code,
    tc.test,
    COUNT(b.*) - tc.total_count as total_diff,
    SUM(CASE WHEN b.success THEN 1 ELSE 0 END) - tc.success_count as success_diff
FROM target_counters tc
JOIN metric_events b 
    ON b.metric_id = tc.metric_id 
    AND b.test = tc.test 
    AND b.code = tc.code 
    AND b.event_at >= tc.start_at 
    AND b.event_at < tc.start_at + interval '1 hour'
GROUP BY tc.start_at, tc.metric_id, tc.code, tc.test, tc.total_count, tc.success_count
HAVING COUNT(b.*) != tc.total_count OR SUM(CASE WHEN b.success THEN 1 ELSE 0 END) != tc.success_count;

3. 更新统计信息 #

确保查询优化器能获取准确的表数据分布，生成最优执行计划：

ANALYZE metric_events;
ANALYZE hourly_metric_event_counters;

对于基数较高的字段（如code、metric_id），可临时调高default_statistics_target参数后重新执行ANALYZE，提升统计精度。

4. 时间分区优化（可选） #

针对持续增长的metric_events，按event_at创建时间分区（按小时/天），查询时仅扫描目标时间段的分区，避免全表扫描：

-- 创建分区父表
CREATE TABLE metric_events (
    id uuid PRIMARY KEY,
    metric_id integer NOT NULL,
    event_at timestamp with time zone NOT NULL,
    code text NOT NULL, 
    test boolean DEFAULT false NOT NULL,
    success boolean DEFAULT false NOT NULL
) PARTITION BY RANGE (event_at);

-- 可通过脚本或pg_partman插件自动管理分区创建

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