PostgreSQL：不用分区键，也能让查询触发分区裁剪

2026-07-09 24 预计阅读时间: 1 分钟
来源: postgr.es AI 摘要 Original link

Disclaimer: This article is an AI-assisted summary. Read it together with the original source when precision matters. The summary may omit context, version differences, or edge cases and is not official documentation.

预计阅读时间：10 分钟

分区表最有价值的能力之一是 pruning：查询条件命中某些分区时，优化器可以直接跳过其他分区。常见经验是“必须按分区键查才有 pruning”，但这并不绝对。如果业务数据在非分区键上和分区键存在稳定相关性，可以用 CHECK 约束把这种事实告诉 PostgreSQL，让它在按非分区键过滤时也能排除整块分区。

这不是魔法，也不是全局索引。它依赖两个前提：数据规律真实存在，并且你愿意用约束维护这条规律。

问题从事件表开始

假设有一张事件表，按时间分区：

CREATE TABLE event (
  id BIGINT GENERATED ALWAYS AS IDENTITY,
  timestamp TIMESTAMPTZ NOT NULL,
  session_id BIGINT NOT NULL,
  type TEXT NOT NULL,
  data JSONB
) PARTITION BY RANGE (timestamp);

CREATE TABLE event_y2025 PARTITION OF event
  FOR VALUES FROM ('2025-01-01 UTC') TO ('2026-01-01 UTC');

CREATE TABLE event_y2026 PARTITION OF event
  FOR VALUES FROM ('2026-01-01 UTC') TO ('2027-01-01 UTC');

按 timestamp 查询时，PostgreSQL 很容易裁剪分区：

EXPLAIN
SELECT *
FROM event
WHERE timestamp >= '2025-12-01 UTC'
  AND timestamp <  '2026-01-01 UTC';

这类查询只需要扫描 event_y2025。因为分区边界本身就是时间范围，优化器可以确定 event_y2026 不可能包含目标数据。

但另一个高频查询通常长这样：

EXPLAIN
SELECT *
FROM event
WHERE session_id = 1000;

如果表按 timestamp 分区，而查询只给了 session_id，PostgreSQL 默认不能知道这个 session 位于哪个时间分区。即使你建了索引：

CREATE INDEX event_session_ix ON event (session_id);

它也只是给每个分区创建本地索引。查询会变成“访问每个分区上的索引”，而不是“跳过不相关分区”。两个分区时问题不大，几十个、几百个分区时就会变成稳定的额外成本。

本地索引不是分区裁剪

本地索引能减少单个分区内的扫描成本，但它不能替优化器回答一个关键问题：这个分区是否根本不可能有 session_id = 1000？

PostgreSQL 截至文中提到的版本仍不支持分区表上的全局索引。全局索引如果存在，可以跨分区定位数据；但没有它时，优化器只能依赖分区边界、约束、统计信息和查询谓词。

这里有一个重要差别：

  • 统计信息只能帮助估算，不能作为排除分区的绝对依据。
  • CHECK 约束是数据库保证为真的事实，优化器可以拿它做逻辑推断。

这就是技巧的入口。

把业务规律变成 CHECK 约束

事件系统里常见的数据规律是：

  • 表是 append-only，事件不会被更新到别的时间。
  • session_id 按时间递增生成。
  • session 生命周期较短，通常几分钟或几小时结束。

于是 session_id 和 timestamp 会有强相关性。虽然表按时间分区，但每个时间分区里大致也对应一段 session id 范围。

可以先查看每个分区的范围：

SELECT tableoid::regclass AS partition_name,
       MIN(session_id) AS min_session_id,
       MAX(session_id) AS max_session_id
FROM event
GROUP BY 1
ORDER BY 1;

假设结果类似：

 partition_name | min_session_id | max_session_id
----------------+----------------+----------------
 event_y2025    |              1 |           4320
 event_y2026    |           4320 |          10000

这时可以给每个分区加约束：

ALTER TABLE event_y2025
  ADD CONSTRAINT event_y2025_session_id_range
  CHECK (session_id BETWEEN 1 AND 4320);

ALTER TABLE event_y2026
  ADD CONSTRAINT event_y2026_session_id_range
  CHECK (session_id BETWEEN 4320 AND 10000);

再查 session_id = 1000：

EXPLAIN
SELECT *
FROM event
WHERE session_id = 1000;

优化器现在可以推断：event_y2026 的 session_id 只可能在 4320 到 10000 之间，因此不可能包含 1000。于是查询只扫描 event_y2025。

查 session_id = 6000 也类似，只会访问 event_y2026。如果查边界值 4320，两个分区都可能包含它，PostgreSQL 会扫描两个分区。这是正确行为，不是失败。

这套机制由 constraint_exclusion 参数控制。对分区表来说，约束排除默认可用；优化器会比较查询条件和分区上的 CHECK 约束，跳过与条件矛盾的分区。

异常值会破坏简单 min/max

真实数据不会永远整齐。比如一个用户在 2025 年末打开页面，session id 是 1；他几天后又回到页面，系统继续写入同一个 session，于是 2026 分区里出现了 session_id = 1。

这时分区范围会变成：

 partition_name | min_session_id | max_session_id
----------------+----------------+----------------
 event_y2025    |              1 |           4320
 event_y2026    |              1 |          10000

如果继续用单一 MIN/MAX，event_y2026 的范围几乎覆盖所有 session id，裁剪效果会大幅下降。

一个可行思路是借鉴 BRIN 索引里的 multi-minmax：不用一个大范围描述整个分区，而是用多个小范围描述“岛”。比如 2026 分区里实际有两个范围：

  • 1 到 1：异常 session。
  • 4320 到 10000：正常递增 session。

约束可以写成：

ALTER TABLE event_y2026
  ADD CONSTRAINT event_y2026_session_id_ranges
  CHECK (
    session_id BETWEEN 1 AND 1
    OR session_id BETWEEN 4320 AND 10000
  );

这样 session_id = 1000 仍然可以排除 event_y2026，因为它既不在 [1, 1]，也不在 [4320, 10000]。而 session_id = 1 会扫描两个分区，因为它确实可能出现在两个分区里。

注意一个边界：文中实验发现，直接用 multirange 类型并不能触发这种 pruning；多个简单条件用 OR 连接才是优化器能理解的形式。

可以这样实践：生成分区约束 SQL

下面这个脚本演示如何为某个分区找出 session id 的连续区间，并生成 ALTER TABLE ... CHECK (...) 命令。你可以把 gap 调大一些，允许小间隔合并，避免约束过碎。

运行前修改两处：

  • partition_name：目标分区表名。
  • gap：超过多大间隔才认为出现新范围。
\set gap 1
\set partition_name event_y2026

WITH distinct_sessions AS (
  SELECT DISTINCT session_id
  FROM :partition_name
), detection AS (
  SELECT
    session_id,
    session_id - LAG(session_id) OVER (ORDER BY session_id) > :gap AS is_gap
  FROM distinct_sessions
), groups AS (
  SELECT
    session_id,
    SUM(COALESCE(is_gap::int, 0)) OVER (ORDER BY session_id) AS group_id
  FROM detection
), ranges AS (
  SELECT
    MIN(session_id) AS mn,
    MAX(session_id) AS mx
  FROM groups
  GROUP BY group_id
)
SELECT
  'ALTER TABLE ' || :'partition_name' ||
  ' ADD CONSTRAINT ' || :'partition_name' || '_session_id_ranges CHECK (' ||
  STRING_AGG('session_id BETWEEN ' || mn || ' AND ' || mx, ' OR ' ORDER BY mn) ||
  ');' AS command
FROM ranges;

如果你确认输出没问题，可以在 psql 中把最后一行接上 \gexec 直接执行。但生产环境里建议先审查生成的 SQL，尤其是范围数量、约束命名、锁表影响。

如果想做一个最小可跑实验，可以用下面命令启动 PostgreSQL 容器：

docker run --rm --name pg-pruning-demo \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  -d postgres:16

psql postgresql://postgres:postgres@localhost:5432/postgres

进入 psql 后创建表、插入测试数据，再用 EXPLAIN 对比添加约束前后的计划。重点看计划里是 Append 扫多个分区，还是直接落到单个分区的 Index Scan。

采用前的检查清单

这个技巧适合“非分区键和分区键高度相关”的数据，比如按时间分区的事件、日志、workflow、审计记录，同时另一个递增 ID 经常被用来查询。

落地时要检查几件事：

  • 数据规律是否稳定：session_id 是否真的随时间递增，是否会大量回填旧 session。
  • 表是否接近 append-only：如果会频繁更新非分区键，维护约束会变复杂。
  • 异常值比例是否可控：少量异常可以用多个范围处理，大量异常会让约束膨胀。
  • 约束生成是否自动化：新分区创建、旧分区封存后，需要及时添加或刷新约束。
  • 查询谓词是否简单：优化器更容易理解 BETWEEN ... OR BETWEEN ... 这类直接表达。
  • 约束变更是否影响写入：ALTER TABLE ... ADD CONSTRAINT 可能需要验证已有数据，生产环境要安排窗口或使用更谨慎的迁移策略。

它不能替代全局索引，也不适合数据分布混乱的表。但在数据天然有序、分区已经按时间落好的系统里，这个方法很实用：你保留按时间分区的维护优势，同时让按 session、workflow id 这类非分区键查询也获得真正的分区裁剪。

# AI # Django # Go # PostgreSQL # 全栈 # 前端 # 后端 # 数据库

相关推荐