如何在 PostgreSQL 中通过非分区列查询时实现剪枝

# 如何在 PostgreSQL 中通过非分区列查询时实现分区裁剪 来源：https://hakibenita.com/postgresql-partition-pruning --- 分区表最有价值的特性之一就是“裁剪”——数据库能够根据查询谓词直接排除整个分区。按常规理解，只有在按分区键查询时才能实现裁剪——这使得选择*正确*的分区键变得异常困难。然而，如果你的数据遵循某些模式，通过一些巧妙技巧，即使在按非分区键列过滤时也能实现裁剪。 **在本文中，我将演示如何在按非分区键列过滤时实现分区裁剪。** 图片由 abstrakt design 提供 图片由 abstrakt design 提供 目录 - 表分区 (https://hakibenita.com/postgresql-partition-pruning#table-partition) - 针对分区键的分区裁剪 (https://hakibenita.com/postgresql-partition-pruning#partition-pruning-for-key-columns) - 本地索引 (https://hakibenita.com/postgresql-partition-pruning#local-indexes) - 全局索引 (https://hakibenita.com/postgresql-partition-pruning#global-indexes) - 在非分区键列上实现裁剪 (https://hakibenita.com/postgresql-partition-pruning#pruning-on-non-partition-key-columns) - 与优化器对话 (https://hakibenita.com/postgresql-partition-pruning#talking-to-the-optimizer) - constraint_exclusion 参数 (https://hakibenita.com/postgresql-partition-pruning#the-constraint_exclusion-parameter) - 引入异常值 (https://hakibenita.com/postgresql-partition-pruning#introducing-outliers) - 处理异常值 (https://hakibenita.com/postgresql-partition-pruning#handling-outliers) - 间隙与孤岛 (https://hakibenita.com/postgresql-partition-pruning#gaps-and-islands) - 背景故事 (https://hakibenita.com/postgresql-partition-pruning#the-backstory) - 最终思考 (https://hakibenita.com/postgresql-partition-pruning#final-thoughts) --- ## 表分区 (https://hakibenita.com/postgresql-partition-pruning#table-partition) 假设你运营着一个拥有大量用户的流行网站。你的产品团队希望深入了解系统的使用情况，于是你开始记录事件。为了给事件提供上下文，你将事件分组到会话中，并将时间、类型和一些数据保存在数据库表中： `` db=# 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; `` 你有许多用户，因此预计会有大量事件。大多数查询只使用数据的一个子集，通常是特定的日期范围，因此你根据时间戳为每一年创建一个分区： `` db=# CREATE TABLE event_y2025 PARTITION OF event FOR VALUES FROM ('2025-01-01 UTC') TO ('2026-01-01 UTC'); CREATE TABLE db=# CREATE TABLE event_y2026 PARTITION OF event FOR VALUES FROM ('2026-01-01 UTC') TO ('2027-01-01 UTC'); CREATE TABLE `` 现在你有两个分区——一个用于 2025 年的事件，另一个用于 2026 年。一个会话可能如下所示： `` INSERT INTO event (session_id, timestamp, type, data) VALUES (1, '2025-12-28 15:00:00 UTC', 'view', '{"page": "/login"}'), (1, '2025-12-28 15:00:06 UTC', 'click', '{"selector": "#login"}'), (1, '2025-12-28 15:00:07 UTC', 'login_failed', '{"attempt": 1}'), (1, '2025-12-28 15:00:10 UTC', 'click', '{"selector": "#forgot-password"}'), (1, '2025-12-28 15:00:17 UTC', 'view', '{"page": "/reset-password"}'), (1, '2025-12-28 15:00:23 UTC', 'click', '{"selector": "#reset-password"}'); `` 在这个会话中，用户尝试登录系统，失败后请求重置密码。 生成更多数据 为了让示例更真实，我们需要更多数据，让我们创建一些： `` WITH sessions AS ( SELECT n AS session_id, '2025-12-28 23:59:56 UTC'::timestamptz + interval '1 minute' * n as started_at FROM generate_series(2, 10_000) AS t(n) ) INSERT INTO event (session_id, timestamp, type, data) SELECT session_id, started_at + interval '1 second' * n, (array['view', 'click', 'login_failed', 'logged_in'])[ceil(random() * 3)] as type, '{}'::jsonb as data FROM sessions, generate_series(1, 5) as n ORDER BY 1, 2; INSERT 0 49995 `` 现在表中大约有 5 万个事件，分布在两个分区中。 ### 针对分区键的分区裁剪 (https://hakibenita.com/postgresql-partition-pruning#partition-pruning-for-key-columns) 表的分区键是 `timestamp`，因此按 `timestamp` 过滤的查询可以受益于分区裁剪。例如，查询 2025 年 12 月的事件： `` db=# EXPLAIN SELECT * FROM event WHERE timestamp >= '2025-12-01 UTC' AND timestamp < '2026-01-01 UTC'; QUERY PLAN ──────────────────────────────────────────────────────────────────────────────────────────────── Seq Scan on event_y2025 event Filter: (("timestamp" >= '2025-12-01 00:00:00+00'::timestamp with time zone) AND ("timestamp" < '2026-01-01 00:00:00+00'::timestamp with time zone)) `` 注意，数据库足够智能，知道只需扫描 2025 年的分区。2026 年的分区甚至没有被访问。**这就是分区裁剪。** 另一个常见查询是查找给定会话的所有事件： `` db=# EXPLAIN SELECT * FROM event WHERE session_id = 1; QUERY PLAN ────────────────────────────────────────────────────── Append (cost=0.00..1060.07 rows=11 width=37) -> Seq Scan on event_y2025 event_1 Filter: (session_id = 1) -> Seq Scan on event_y2026 event_2 Filter: (session_id = 1) `` 这次，数据库访问了所有分区——没有使用分区裁剪。在这个查询中，数据库无法排除任何分区，因此别无选择，只能扫描所有分区来查找匹配的事件。 **这就是分区变得有点棘手的地方。** 一方面，你希望实现裁剪，但另一方面，你不得不在其他可能非常常见的查询中做出痛苦的妥协。 ### 本地索引 (https://hakibenita.com/postgresql-partition-pruning#local-indexes) 获取特定会话的事件相当常见，因此需要快速完成。在数据库中，要加快速度，通常只需创建索引，对吧？ `` db=# CREATE INDEX event_session_ix ON event(session_id); CREATE INDEX `` 这会在会话 ID 上创建一个索引。有了索引之后，获取会话 1 的事件： `` db=# EXPLAIN SELECT * FROM event WHERE session_id = 1; QUERY PLAN ───────────────────────────────────────────────────────────────────────── Append (cost=0.29..16.82 rows=11 width=37) -> Index Scan using event_y2025_session_id_idx on event_y2025 event_1 Index Cond: (session_id = 1) -> Index Scan using event_y2026_session_id_idx on event_y2026 event_2 Index Cond: (session_id = 1) `` 数据库再次不得不访问所有分区。唯一的区别是，这次它在每个分区上使用了索引。 使用索引比扫描整个分区更快，但数据库仍然被迫扫描所有分区。目前只有两个分区，但如果表有一百个分区，这个查询就像查询一百个表一样！ 这种索引称为本地索引，因为它会在每个分区上创建单独的索引： `` db=# \di event_* List of indexes Schema │ Name │ Type │ Owner │ Table ────────┼────────────────────────────┼───────────────────┼───────┼───────────── public │ event_session_ix │ partitioned index │ haki │ event public │ event_y2025_session_id_idx │ index │ haki │ event_y2025 public │ event_y2026_session_id_idx │ index │ haki │ event_y2026 `` 当你经常按非分区键列进行过滤时，本地索引非常有用。 ### 全局索引 (https://hakibenita.com/postgresql-partition-pruning#global-indexes) 另一种为分区表建立索引的方法是创建一个跨越多个分区的单一索引。这称为全局索引。 不幸的是，截至版本 19，PostgreSQL 不支持在分区表上创建全局索引。你可以关注 pgsql-hackers 邮件列表以获取更新，关于这个主题的讨论早在 2009 年就已经开始了。 全局索引 没有全局索引的另一个痛点在于，它使得在分区键以外的列上强制唯一性变得困难。这超出了本文的范围。 ## 在非分区键列上实现裁剪 (https://hakibenita.com/postgresql-partition-pruning#pruning-on-non-partition-key-columns) 事件表按时间戳分区，因此按时间戳查询可以受益于分区裁剪。然而，仍然有很多情况需要按会话 ID 查询。单个会话的事件可能跨越多个分区，而数据库目前无法排除不相关的分区。本地索引缓解了部分痛苦，但数据库仍然需要访问所有分区，这可能无法很好地扩展。 此时，你已经达到了数据库开箱即用所能做到的极限，需要利用你的领域知识和数据知识——数据是如何使用和存储的： - **事件表是只追加的**：没有更新操作，事件是不可变的。 - **会话 ID 是顺序生成的**：会话 ID 随时间递增。 - **会话是短命的**：一个正常会话通常只持续几分钟或几小时。 这个模式可能很有用！ 为了可视化这个模式，将时间戳与会话 ID 绘制成图： 将时间戳与会话 ID 绘制成图 将时间戳与会话 ID 绘制成图 **会话 ID 与时间戳高度相关。** 这意味着应该可以为每个分区识别出不同的会话 ID 范围。 获取每个分区中第一个和最后一个会话 ID： `` db=# SELECT tableoid::regclass, MIN(session_id), MAX(session_id) FROM event GROUP BY 1 ORDER BY 1; tableoid │ min │ max ─────────────┼──────┼─────── event_y2025 │ 1 │ 4320 event_y2026 │ 4320 │ 10000 `` 由于会话 ID 与时间戳之间的强相关性，你可以为每个分区识别出清晰且不同的会话 ID 范围。仅从结果来看，很明显会话 ID 在 1 到 4319 之间的事件只存在于 2025 年分区中，而会话 ID 在 4321 到 10000 之间的事件只存在于 2026 年分区中。 按分区划分的会话 ID 范围 按分区划分的会话 ID 范围 了解了这个模式，数据库在按会话 ID 查询时就有可能排除整个分区，但你如何将这些信息传达给数据库呢？ ### 与优化器对话 (https://hakibenita.com/postgresql-partition-pruning#talking-to-the-optimizer) 数据库优化器确实是一个了不起的工程奇迹。它接收查询，然后自行决定如何执行，而无需查看实际数据。数据库唯一能使用的，是它维护的表和列上的统计信息。 统计信息用于生成行估计值。行估计值帮助优化器决定首先扫描哪些表、使用哪些索引、应用哪些过滤器或连接以及它们的顺序。然而，正如其名，统计信息只是统计——不能保证它们正确，因此优化器可以咨询它们，但绝不能盲目依赖。 优化器唯一可以信赖的信息是保证永远正确的信息。在数据库中，要保证某件事永远正确，你需要使用约束。 检查约束用于强制自定义验证规则。通过使用检查约束，你提供一个表达式，数据库保证该表达式对于表中的所有行都为真。但这里有一个秘密，一个没人告诉你的东西——**检查约束可用于将关于*你的数据*的信息传达给优化器。** 例如，如果你有一个检查约束确保事件 ID 始终大于零，那么如果你查询 ID 小于零的事件，数据库实际上不需要扫描表就能知道没有行能匹配这个条件，对吧？ 要检查你是否能影响优化器，在每个分区上添加一个简单的检查约束，以强制会话 ID 的特定范围： `` db=# ALTER TABLE event_y2025 ADD CONSTRAINT event_y2025_session_id_range CHECK (session_id between 1 and 4320); ALTER TABLE db=# ALTER TABLE event_y2026 ADD CONSTRAINT event_y2026_session_id_range CHECK (session_id between 4320 and 10000); ALTER TABLE `` 第一个检查约束将 2025 年分区的会话 ID 范围设置为 1 到 4320。第二个检查约束将 2026 年分区的会话 ID 范围设置为 4320 到 10000。 现在，为了测试数据库是否真的能利用检查约束来排除整个分区，查询会话 ID 为 1000 的事件： `` db=# EXPLAIN SELECT * FROM event WHERE session_id = 1000; QUERY PLAN ────────────────────────────────────────────────────────────────── Index Scan using event_y2025_session_id_idx on event_y2025 event Index Cond: (session_id = 1000) `` 太棒了！有了检查约束，数据库能够排除 2026 年的分区。根据约束，该分区只能包含会话 ID 在 4320 到 10000 之间的行，而查询查找的是会话 ID 1000。数据库最终只扫描了一个分区。 检查另一个会话 ID： `` db=# EXPLAIN SELECT * FROM event WHERE session_id = 6000; QUERY PLAN ────────────────────────────────────────────────────────────────── Index Scan using event_y2026_session_id_idx on event_y2026 event Index Cond: (session_id = 6000) `` 再一次，数据库只扫描了一个分区。这次，根据检查约束，会话 ID 6000 的事件只能存在于 2026 年的分区中，因此数据库只扫描了该分区。 如果你查询一个可能涉及多个分区的事件会话呢？ `` db=# EXPLAIN SELECT * FROM event WHERE session_id = 4320; QUERY PLAN ───────────────────────────────────────────────────────────────────────── Append (cost=0.29..16.80 rows=10 width=37) -> Index Scan using event_y2025_session_id_idx on event_y2025 event_1 Index Cond: (session_id = 4320) -> Index Scan using event_y2026_session_id_idx on event_y2026 event_2 Index Cond: (session_id = 4320) `` 数据库扫描了两个分区，这没问题！根据检查约束，这两个分区都可能包含会话 ID 4320 的事件。 这证明了通过精心设计的检查约束，你可以将数据信息传达给数据库，而数据库可以利用这些信息排除整个分区，从而有效地实现分区裁剪。 ### `constraint_exclusion` 参数 (https://hakibenita.com/postgresql-partition-pruning#the-constraint_exclusion-parameter) 这种裁剪机制由参数 `constraint_exclusion` 控制 (https://www.postgresql.org/docs/17/runtime-config-query.html#GUC-CONSTRAINT-EXCLUSION)： > 控制查询规划器使用表约束来优化查询。[...] 当此参数允许对特定表使用时，规划器会将查询条件与表的 CHECK 约束进行比较，并省略扫描条件与约束矛盾的表的扫描。 Constraint exclusion 默认对分区表开启。数据库会使用你在分区表上添加的任何检查约束。