PostgreSQL / 系统课时

排查分区过多与规划变慢

排查分区过多与规划变慢

查询执行只花几毫秒,总响应却明显变慢,时间可能耗在规划大量分区上。本课先拆开规划与执行,再检查裁剪条件和参数计划,通过同负载对照决定调整查询还是合并粒度,避免把所有慢查询都归因于分区数。

学习目标

  • 分别读取 Planning Time 与 Execution Time
  • 验证查询条件是否有效缩小分区范围
  • 用叶子数量和对照数据决定结构调整

建立可重复的小实验

以下在 PostgreSQL 18 的独立练习库执行。只建两个分区来验证诊断方法;它不会故意制造规划压力,也不能作为生产容量测试结果。

sql
CREATE TABLE planning_events_lab (day date NOT NULL, id bigint)
PARTITION BY RANGE (day);
CREATE TABLE planning_events_lab_jan PARTITION OF planning_events_lab
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE planning_events_lab_feb PARTITION OF planning_events_lab
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
INSERT INTO planning_events_lab VALUES ('2026-01-15', 1), ('2026-02-15', 2);
ANALYZE planning_events_lab;
EXPLAIN (ANALYZE, BUFFERS, SUMMARY, COSTS OFF)
SELECT count(*) FROM planning_events_lab
WHERE day >= DATE '2026-01-01' AND day < DATE '2026-02-01';

预期结果:返回执行计划,访问一月叶子,包含 Planning Time 与 Execution Time;具体毫秒数随实例变化。EXPLAIN ANALYZE 真正执行语句,本课只测 SELECT,不能把它无条件套到生产 UPDATE 或 DELETE 上。输出解释见 EXPLAIN 文档。

用同一条查询拆开时间

连续运行至少 5 次,分别保存规划和执行时间,报告中位数并标明首次运行。首次可能包含目录与数据页的冷访问,不能只选择最快一次。若规划中位数 35 ms、执行 4 ms,优化执行索引最多只解决较小部分;若执行 3 秒、规划 5 ms,应先调查扫描与连接。

应用端延迟还包括连接池、网络、排队和结果传输。将数据库测量与请求追踪对应,防止把连接等待误认成规划耗时。复杂 JOIN、表达式、权限和目录访问也可能影响规划,不只分区数量一个变量。

检查实际叶子与裁剪条件

sql
SELECT count(*) FILTER (WHERE isleaf) AS leaf_count,
       max(level) AS max_level
FROM pg_partition_tree('planning_events_lab');
SHOW enable_partition_pruning;

EXPLAIN (COSTS OFF)
SELECT count(*) FROM planning_events_lab
WHERE to_char(day, 'YYYY-MM') = '2026-01';

预期结果:leaf_count=2、max_level=1,裁剪设置通常为 on;第二条计划会检查两个叶子上的表达式过滤。把查询改回直接针对 day 的半开区间,再比较访问范围与时间。不要为了调试全局关闭裁剪;配置用途见 查询规划参数。

区分参数化计划与静态计划

sql
PREPARE planning_month(date, date) AS
SELECT count(*) FROM planning_events_lab WHERE day >= $1 AND day < $2;
EXPLAIN (ANALYZE, SUMMARY, COSTS OFF)
EXECUTE planning_month(DATE '2026-01-01', DATE '2026-02-01');
DEALLOCATE planning_month;

预期结果:成功输出参数化查询的执行计划,count 的结果应为 1。实际可能使用定制计划或通用计划,不把某一条输出写死。执行期裁剪可以减少实际扫描,但计划仍可能保留多个子计划;结合 Subplans Removed、never executed 与 loops 理解访问情况。

使用长连接和服务端预备语句的应用,应在相同连接生命周期下测量。直接在新 psql 连接执行一次字面量查询,不能代表连接池使用参数计划后的表现。

有证据再改变分区粒度

若保留 36 个月、每天一个分区约 1095 个叶子,而主要查询和归档都按月,月分区约 36 个叶子值得对照。对照时固定数据量、索引、SQL 与缓存条件,同时测 1 天、1 月和跨年的请求。

合并分区通常涉及建新表、搬迁、校验和路由切换,不是简单改一个粒度参数。交付中给出迁移量、写入切换与回退方案;验收包含规划下降幅度、执行代价、维护窗口以及未来对象增长。只有对象数量持续可控,才算解决了规划成本的来源。

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

目录与阅读设置

记录一个想法

排查分区过多与规划变慢

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

登录哪都通