如何提升PostgreSQL12继承分区表相对50M行聚簇表的读性能

问题场景 #

我在PostgreSQL 12实例中存储时序指标数据，表结构如下：

CREATE TABLE metric_values
(
    event_id bigint NOT NULL,
    "timestamp" timestamp without time zone NOT NULL,
    value numeric(20,4) NOT NULL
);

CREATE TABLE metric_values_old
(
    event_id bigint NOT NULL,
    "timestamp" timestamp without time zone NOT NULL,
    value numeric(20,4) NOT NULL
);

两张表结构完全一致，均建有event_id和timestamp列的联合索引。区别在于metric_values_old约有5000万行数据，基于上述索引聚簇；而metric_values采用继承方式分区，每个子表都单独创建了索引和聚簇规则，每个分区按timestamp列存储单年度数据。

以下是其中一张子表的定义：

CREATE TABLE metric_values_2021
(
    -- Inherited from table metric_values: event_id bigint NOT NULL,
    -- Inherited from table metric_values: "timestamp" timestamp without time zone NOT NULL,
    -- Inherited from table metric_values: value numeric(20,4) NOT NULL,
    CONSTRAINT metric_values_2021_event_id_timestamp_key UNIQUE (event_id, "timestamp"),
    CONSTRAINT metric_values_2021_timestamp_check CHECK (date_part('year'::text, "timestamp") = 2021)
)
INHERITS (metric_values)
TABLESPACE pg_default;

CREATE INDEX metric_values_2021_idx
ON metric_values_2021 USING btree
(event_id ASC NULLS LAST, "timestamp" ASC NULLS LAST)
TABLESPACE pg_default;

ALTER TABLE metric_values_2021
CLUSTER ON metric_values_2021_idx;

对比两张表的查询性能时，分区表表现比聚簇表更差。原本预期带timestamp条件的查询可以只命中对应子表、性能更优，且分区表后续维护更便捷，不会像聚簇表那样持续膨胀到5000万行以上。

我在两张表上分别执行了以下查询：

select event_id, timestamp, value from metric_values
WHERE timestamp between '2020-08-01' and '2020-08-31';

select event_id, timestamp, value from metric_values_old
WHERE timestamp between '2020-08-01' and '2020-08-31';

两张表的执行计划如下：

• 无分区的聚簇表【执行计划截图】
• 分区表【执行计划截图】

看起来分区表扫描了所有分区导致成本升高。按照社区建议优化后查询性能有了大幅提升，但PostgreSQL似乎仍会扫描所有分区，需要该现象的原因和规避方法。

问题根因 #

1. 继承式分区的约束排除逻辑依赖CHECK约束的可推断性，当前分区约束使用date_part()函数包裹timestamp字段做判断，查询条件是直接对timestamp字段做区间过滤，PostgreSQL查询规划器无法推断出函数表达式和查询条件的关联，无法在规划阶段裁剪掉不需要的分区。
2. 虽然运行时会跳过不符合约束的分区，不会真的读取数据，但规划阶段遍历所有分区、运行时检查每个分区约束都会带来额外开销，导致性能低于普通聚簇表。

优化方案 #

• 调整分区CHECK约束写法，替换函数表达式为直接的时间区间判断，以2021分区为例：

ALTER TABLE metric_values_2021 DROP CONSTRAINT metric_values_2021_timestamp_check;
ALTER TABLE metric_values_2021 ADD CONSTRAINT metric_values_2021_timestamp_check 
CHECK ("timestamp" >= '2021-01-01'::timestamp AND "timestamp" < '2022-01-01'::timestamp);

所有分区调整完成后重新执行查询，规划器就可以直接根据查询的时间区间裁剪掉所有不相关的分区，执行计划只会显示命中的分区。

• 验证约束排除配置：执行show constraint_exclusion;确认返回值为partition，这是PostgreSQL默认配置，专门针对继承/分区表做约束排除，不需要改为on避免不必要的性能损耗。
• 长期优化建议：迁移到PostgreSQL原生声明式范围分区，PostgreSQL 12对声明式分区的优化非常完善，不需要手动维护继承关系、CHECK约束，分区裁剪效率更高，后续分区维护（新增、归档、拆分）操作也更简单，示例创建逻辑：

-- 创建声明式分区父表
CREATE TABLE metric_values_new (
    event_id bigint NOT NULL,
    "timestamp" timestamp without time zone NOT NULL,
    value numeric(20,4) NOT NULL
) PARTITION BY RANGE ("timestamp");

-- 按年度创建分区
CREATE TABLE metric_values_new_2020 PARTITION OF metric_values_new
FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
CREATE TABLE metric_values_new_2021 PARTITION OF metric_values_new
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

-- 为分区创建联合索引、设置聚簇，和原有逻辑一致
CREATE INDEX metric_values_new_2020_idx ON metric_values_new_2020 USING btree (event_id, "timestamp");
ALTER TABLE metric_values_new_2020 CLUSTER ON metric_values_new_2020_idx;

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