云贝教育。头像
分区表都建好了,查询还是扫全表:PostgreSQL 裁剪那一步漏在哪封面图

分区表都建好了,查询还是扫全表:PostgreSQL 裁剪那一步漏在哪

按月切了 36 个分区,报表查上个月反而比切之前更慢。EXPLAIN 一拉,Append 底下 36 个子计划一个不少,每个都规规矩矩来一遍 Seq Scan。分区是建了,数据也确实按月份落在各自分区里,可原本该省下来的那一步——分区裁剪(partition pruning)——压根没生效。

分区不会自动让查询变快,真正省掉扫描的是裁剪。这篇按"怎么确认没裁、为什么没裁、怎么改"过一遍。

这类现场在云贝教育的日常排障复盘里出现过不少次,结论都差不多:先看计划,别先加索引。

一、父表不存数据,裁剪才是省力的那一步

声明式分区里,父表是一张"虚拟"表,没有自己的存储,数据全在各个子分区。对父表的查询会变成对若干子分区的 Append;规划器根据查询条件和分区边界,把不可能命中的分区从计划里去掉,这个动作就是裁剪。enable_partition_pruning 默认 on,一旦被关掉,所有分区都会被扫一遍。

裁剪发生在两个时机,看执行计划时的判断标准也不一样:

  • 规划期裁剪:条件是规划器能看到的常量。被裁掉的分区根本不出现在执行计划里,数一下子计划数量就知道。
  • 执行期裁剪:条件里含 PREPARE 参数、子查询结果、参数化嵌套循环内侧的值。计划里会保留所有子分区,要靠 EXPLAIN (ANALYZE) 判断:初始化阶段裁掉的数量看 Subplans Removed: N,每次循环都裁掉的分区显示 (never executed),loops 会明显少于兄弟节点。

有个容易踩的细节:在执行初始化阶段被裁掉的分区,仍会在执行开始时被加锁。裁剪省的是扫描,不等于省掉关系锁开销。

二、常见的六个"没裁掉"原因

1. `WHERE` 里压根没带分区键。 按 occurred_at 按月分区,业务查询却只带 tenant_id,那每个分区都得看一遍。分区键要跟高频 WHERE 条件对齐,这是建表时就得定的事。

2. 分区键上套了函数或做了运算。 规划器没法把表达式和分区边界对上:

-- 这样写,裁剪基本指望不上

SELECT count(*) FROM orders

WHERE date_trunc('month', occurred_at) = DATE '2026-10-01';

-- 改成对裸列做范围比较

SELECT count(*) FROM orders

WHERE occurred_at >= DATE '2026-10-01'

  AND occurred_at <  DATE '2026-11-01';

3. 类型 / 时区不一致。 分区键是 timestamptz,条件却传 timestamp 或字符串,隐式转换会破坏边界推导。显式写成同一类型,别让规划器猜。

4. 条件是运行时才有的值。 WHERE occurred_at >= now() - interval '7 days' 在规划期值未知,只能靠执行期裁剪。这时候 plain EXPLAIN 看着像全扫,得上 EXPLAIN (ANALYZE) 才能确认到底扫了几个。

5. 分区切得太碎。 分区数上千时,光是把计划拼出来就够慢,裁剪省下的扫描时间会被规划开销吃掉一部分。按月或按季比较稳,别按天切出几千个。

6. 调错了开关。 constraint_exclusion 是老式继承分区用的 CHECK 约束排除,默认 partition,只在规划期生效;声明式分区该看的是 enable_partition_pruning。两个参数别混。

另外三条建表期的硬规则,踩了会直接报错或白干:

  • 主键 / 唯一约束必须包含分区键列,不含分区键建表就报错,别指望"全局唯一索引";
  • 在父表上 `CREATE INDEX` 会自动传播到所有分区,包括以后新建或 ATTACH 进来的,不用逐个子表建;
  • 归档冷数据用 DETACH PARTITION(加 CONCURRENTLY 只需父表 SHARE UPDATE EXCLUSIVE 锁),补分区用 ATTACH PARTITION(同样只需 SHARE UPDATE EXCLUSIVE),比 DROP TABLE 的 ACCESS EXCLUSIVE 友好得多。若表上挂了 DEFAULT 分区,ATTACH 前得先确认默认分区里没有落在新区间的数据,否则校验会挡住你。

三、用 EXPLAIN 把话说死

常量条件,看规划期裁剪:

EXPLAIN (COSTS OFF)

SELECT count(*) FROM orders

WHERE occurred_at >= DATE '2026-10-01'

  AND occurred_at <  DATE '2026-11-01';

做个对照实验,一眼看出裁剪到底替你省了多少:

BEGIN;

SET LOCAL enable_partition_pruning = off;

EXPLAIN (COSTS OFF)

SELECT count(*) FROM orders

WHERE occurred_at >= DATE '2026-10-01'

  AND occurred_at <  DATE '2026-11-01';

ROLLBACK;

参数化 / 泛型计划,看执行期裁剪:

SHOW enable_partition_pruning;             -- 默认 on

SET plan_cache_mode = force_generic_plan;

PREPARE q(date, date) AS

  SELECT count(*) FROM orders

  WHERE occurred_at >= $1 AND occurred_at < $2;

EXPLAIN (ANALYZE, COSTS OFF)

  EXECUTE q(DATE '2026-10-01', DATE '2026-11-01');

-- 关注 Subplans Removed 与 (never executed)

建索引与维护分区:

-- 父表建索引,自动传播到所有分区

CREATE INDEX ON orders (occurred_at);

-- 唯一约束必须包含分区键列

CREATE TABLE orders (

  id           bigint NOT NULL,

  occurred_at  date   NOT NULL,

  amount       numeric(12,2),

  PRIMARY KEY (id, occurred_at)

) PARTITION BY RANGE (occurred_at);

-- 补一个空分区

CREATE TABLE orders_2026_12 PARTITION OF orders

  FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

-- 已有表先灌数再挂进来,只需 SHARE UPDATE EXCLUSIVE 锁

CREATE TABLE orders_2026_11 (LIKE orders INCLUDING DEFAULTS);

ALTER TABLE orders ATTACH PARTITION orders_2026_11

  FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

-- 冷数据归档,CONCURRENTLY 降低锁级别

ALTER TABLE orders DETACH PARTITION orders_2026_09 CONCURRENTLY;

反例与推荐做法

排查五步(照着查就行)

1. 看计划:数一下子计划数量,是不是等于分区总数;

2. 查开关:SHOW enable_partition_pruning,确认是 on;

3. 查条件:WHERE 有没有带分区键,有没有套函数、运算或隐式类型转换;

4. 分时机:常量条件看规划期,参数 / 子查询看 Subplans Removed 与 never executed;

5. 查设计:分区键对不对得上高频查询、分区数是不是太多、索引是不是建在父表上。

常见故障速查

观察点

说明

计划里子分区数 = 分区总数

裁剪没生效,先查条件和开关

Subplans Removed: N

执行初始化阶段裁掉 N 个分区,仍会加关系锁

(never executed)

该分区每次循环都被裁掉

enable_partition_pruning

默认 on,声明式分区专用

constraint_exclusion

老式继承分区的 CHECK 排除,默认 partition,只在规划期

主键 / 唯一约束

必须包含分区键列,否则建表报错

父表 CREATE INDEX

自动传播到所有分区及后续新建分区

写在最后

分区表慢下来,多数不是分区建错了,而是查询写法让规划器没法把边界对上。别急着加索引或者重切分区,先跑一遍 EXPLAIN:计划里还剩几个子分区,答案基本就在那儿。说白了,切得再细,条件不带分区键也是白切。

—— 云贝教育,专注 IT 运维实战。

转载自 CSDN-专业IT技术社区

原文链接:https://blog.csdn.net/hdhdhdk/article/details/170850232

文章来源转载

评论

评论列表

微信小程序
QQ小程序

关于作者

点赞数:0
关注数:0
粉丝:0
文章:0
关注标签:0
加入于:--