PostgreSQL / 系统课时

分区裁剪

分区裁剪

分区表并不会天然避免扫描所有分区,真正决定性能的是查询条件能否让 PostgreSQL 在计划期或执行期排除无关分区。本课用可复现 SQL 验证两类裁剪,并给出发现全分区扫描时的定位顺序。

学习目标

  • 能从 EXPLAIN 与 EXPLAIN ANALYZE 中识别分区是否被裁剪
  • 理解计划期裁剪与执行期裁剪发生的时机和差异
  • 能按分区键、谓词写法和参数化执行顺序排查全分区扫描

准备可观察的分区表

下面创建按月份范围分区的订单表,并插入少量数据。示例使用 date 类型,避免时区转换干扰裁剪判断。

sql
DROP TABLE IF EXISTS orders_part CASCADE;

CREATE TABLE orders_part (
    order_id bigint NOT NULL,
    created_on date NOT NULL,
    amount numeric(10,2) NOT NULL
) PARTITION BY RANGE (created_on);

CREATE TABLE orders_2026_01 PARTITION OF orders_part
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE orders_2026_02 PARTITION OF orders_part
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE orders_2026_03 PARTITION OF orders_part
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

INSERT INTO orders_part VALUES
(1, '2026-01-10', 99.00),
(2, '2026-02-15', 120.00),
(3, '2026-03-20', 88.00);

预期结果

text
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 3

不同客户端可能省略部分成功提示,但应成功创建 3 个分区并插入 3 行。

验证计划期裁剪

当分区键条件在生成执行计划时已经是常量,规划器可以直接排除不可能命中的分区。

sql
EXPLAIN
SELECT *
FROM orders_part
WHERE created_on >= DATE '2026-02-01'
  AND created_on <  DATE '2026-03-01';

预期结果

text
Seq Scan on orders_2026_02
  Filter: ((created_on >= '2026-02-01'::date) AND (created_on < '2026-03-01'::date))

成本数字会因环境不同而变化,关键不是数值,而是计划中只出现 orders_2026_02。这说明无关分区已在计划生成阶段被排除。

计划期与执行期分区裁剪

验证执行期裁剪

要稳定观察执行期裁剪,可以强制预处理语句使用通用计划。此时规划器只看到参数 $1、$2,执行器拿到具体日期后再移除无关子计划。

sql
SET plan_cache_mode = force_generic_plan;

PREPARE find_orders(date, date) AS
SELECT *
FROM orders_part
WHERE created_on >= $1
  AND created_on <  $2;

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF)
EXECUTE find_orders(DATE '2026-03-01', DATE '2026-04-01');

DEALLOCATE find_orders;
RESET plan_cache_mode;

预期结果

text
SET
PREPARE
Append (actual time=<TIME>..<TIME> rows=1 loops=1)
  Subplans Removed: 2
  ->  Seq Scan on orders_2026_03 (actual time=<TIME>..<TIME> rows=1 loops=1)
        Filter: ((created_on >= $1) AND (created_on < $2))
DEALLOCATE
RESET

<TIME> 会随机器和缓存状态变化。这里最重要的是 Subplans Removed: 2:通用计划保留了分区结构,但执行初始化阶段根据参数排除了另外两个分区。若参数来自嵌套循环的外层行,裁剪还可能在执行过程中重复发生。

排查全分区扫描

发现 Append 或 Parallel Append 下大量分区都被扫描时,先检查查询条件是否直接约束分区键。把分区键包在函数或复杂表达式中,可能让规划器无法证明某个分区一定不匹配。

sql
EXPLAIN
SELECT *
FROM orders_part
WHERE date_trunc('month', created_on::timestamp) = TIMESTAMP '2026-02-01';

预期结果

text
Append
  ->  Seq Scan on orders_2026_01
  ->  Seq Scan on orders_2026_02
  ->  Seq Scan on orders_2026_03

具体计划中的成本与过滤条件可能不同,但这类写法会让所有三个分区进入计划。改写为 created_on >= DATE '2026-02-01' AND created_on < DATE '2026-03-01' 更容易得到确定的裁剪结果。

排查时按固定顺序检查:先确认 enable_partition_pruning 未被关闭,再看谓词是否直接约束分区键,然后检查运行时参数是否可用于执行期裁剪,最后用 EXPLAIN (ANALYZE, BUFFERS) 验证实际访问的分区与缓冲区。只有执行计划真正排除了无关分区,分区表才把大表访问缩小到少量叶子分区。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

分区裁剪

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通