PostgreSQL 18 十三组实测：部分索引省 95% 空间，BRIN 索引 24KB 就够用
原文信息：本文译自《Thirteen small measurements on PostgreSQL 18》，作者 Hollis Varden，发布于 2026 年 11 月 8 日。原文链接：https://explainmeasured.com/posts/thirteen-measurements-pg18/ 。原文未注明许可信息。本文为翻译，译者在文末补充了译者注。
译者导语：PostgreSQL 18 发布一个多月，官方发布说明里列了不少新特性，但真正落到日常查询上，哪些设置值得动、哪些索引值得建，光看文档心里还是没底。Hollis Varden 干脆自己动手，在一台 MacBook Air 上用 200 万行订单表做了十三组小测量：索引、写入、排序、并行，每组都有具体数字。没有宏大结论，但每组结果都够直接拿去对照自己的库。
测试环境
PostgreSQL 18.6，从源码编译，默认配置（work_mem 4MB、shared_buffers 128MB、每次 Gather 2 个并行 worker）。机器是 MacBook Air M5，10 核、24GB 内存、内置 SSD。这台机器跟作者以前文章用的不是同一台，所以不要拿这里的耗时跟他以前的文章横向比较。
多数测试用同一张 200 万行订单表：5 万个客户，5% 的行状态为 open，created_at 每 10 秒递增一条，note 字段里是一串 md5 字符串。查询耗时取 EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) 的执行时间，中位数：预热一次后跑 5 次取中位数，慢的测试跑 3 次。Buffers 取顶层计划节点的数值。大小单位是十进制 MB。测试脚本和原始结果见文末。
索引
部分索引（partial index）只存 WHERE 子句允许的行。
| customer_id 上的索引 | 大小 | 查一个客户的未完成订单所用 Buffers |
|---|---|---|
| 全部行 | 14.2 MB | 43 |
WHERE status = 'open' |
0.7 MB | 2 |
只有查询的 WHERE 条件跟索引条件对得上时，这个索引才会被用上。
BRIN 索引不给每行都建条目，而是按块范围（block range）存一份摘要。测试数据是按 created_at 顺序插进去的，正是 BRIN 最擅长的场景。
| created_at 上的索引 | 大小 | 统计一天的数据（8,640 行） |
|---|---|---|
| B 树 | 44.9 MB | 0.55 ms，134 buffers |
| BRIN | 24 kB | 0.63 ms，137 buffers |
表本身是 202.3 MB。作者没测打乱顺序的数据——那种情况下 BRIN 拿不出有用的摘要。
INCLUDE 可以让索引带上一个不参与排序的列。统计某一个客户 40 行的 sum(amount)：
| 索引 | 大小 | Buffers | 耗时 |
|---|---|---|---|
(customer_id) |
14.2 MB | 43 | 0.073 ms |
(customer_id) INCLUDE (amount) |
45.0 MB | 4 | 0.038 ms |
体积暴涨的原因是去重（deduplication）。从 PostgreSQL 13 起，B 树会把重复的键只存一次，后面挂一串行指针，而 INCLUDE 索引做不到这一点。手动关掉去重再测：
| 索引 | 开启去重 | 关闭去重 |
|---|---|---|
| status（2 个取值） | 13.9 MB | 45.0 MB |
| customer_id（50,000 个取值） | 14.2 MB | 45.0 MB |
| 主键（全部唯一） | 44.9 MB |
索引仅扫描（index-only scan）也不是真的完全不碰表：只要还有页面没被 VACUUM 标成全部可见（all-visible），扫描时就得回表确认。在一台刚导完数据、关了 autovacuum 的表上扫描 200,001 行：
| Heap fetches | Buffers | 耗时 | |
|---|---|---|---|
| VACUUM 之前 | 200,001 | 1,917 | 7.1 ms |
| VACUUM 之后 | 0 | 553 | 4.4 ms |
两次的执行计划都写着 Index Only Scan。
PostgreSQL 会给主键自动建索引，但不会给外键列建。10,000 个父行、1,000,000 个子行，ON DELETE CASCADE，删除 200 个父行（连带 20,000 个子行）：child.parent_id 上没有索引花了 3.6 秒，有索引只花了 0.013 秒（相差约 277 倍）。
写入
用 psql 通过本地 socket 导 200,000 行，每次一个事务，取 3 次中位数：
| 方法 | 耗时 |
|---|---|
\copy |
0.039 s |
| INSERT，每条语句 1,000 行 | 0.133 s |
| INSERT，每条语句 1 行 | 2.1 s |
往空表插 2,000,000 行：
| 表 | 耗时 | WAL |
|---|---|---|
| 普通表 | 1.78 s | 192.6 MB |
| UNLOGGED 表 | 1.00 s | 0 MB |
UNLOGGED 表崩溃后会被清空，而且不参与复制。
再看提交：用 pgbench 跑 10,000 次单行 INSERT，一个客户端，每条语句自成一个事务。synchronous_commit = on 时每秒 28,101 个事务，off 时 64,478 个。关掉它，崩溃时可能丢掉最后几次提交，但不会损坏数据。这台 Mac 的 fsync 很便宜（wal_sync_method 是 open_datasync），换到真正会落盘的服务器磁盘上，差距可能不一样——作者没测那一种。
最后是对一张没有索引的表全表 UPDATE 每一行，表大小的变化：
| 步骤 | 表大小 |
|---|---|
| 更新前 | 202.5 MB |
| UPDATE 之后 | 404.7 MB |
| VACUUM 之后 | 404.7 MB |
| 第二次全表 UPDATE 之后 | 404.7 MB |
每次更新都会写一个新版本的行。VACUUM 没有把空间还给操作系统，但让第二次 UPDATE 复用了这些空间。
排序与并行
关掉并行，对 200 万行按 note 排序。ORDER BY note LIMIT 10 花了 119 ms，用的是 26 kB 的 top-N 堆排序（heapsort）；全量排序花了 571 ms。两者都要读遍每一行。
同样的全量排序，换三档 work_mem：
| work_mem | 耗时 | 排序方式 |
|---|---|---|
| 4MB（默认） | 433 ms | 外部归并，74 MB 落盘 |
| 64MB | 297 ms | 快速排序，50 MB 在内存里 |
| 256MB | 295 ms | 快速排序，50 MB 在内存里 |
超过排序实际需要的内存，再加也没用。注意 work_mem 是按每个查询里的每次排序或哈希单独算的，所以全局设得太大，代价会成倍放大。
最后是对全部 200 万行做 LIKE '%ab%' 过滤再 GROUP BY：
| max_parallel_workers_per_gather | 耗时 |
|---|---|
| 0 | 151 ms |
| 2（默认） | 54 ms |
| 4 | 44 ms |
每次运行读的页面都一样。两个并行 worker 拿走了大部分收益。
测试脚本与原始数据
测试脚本和原始结果见原文链接：run.py：上面所有测试，按顺序；brin_size.py：BRIN 索引的精确字节数（主测试里四舍五入成了 0.0 MB）；results.json：原始输出，包括每一次运行。
译者注
- 数字都是相对值：测试机是 MacBook Air M5、默认配置，作者自己也提醒别拿这组数字跟他以前的文章横向比。落到自己的环境，重点看趋势和量级——比如部分索引省 95% 的空间、外键缺索引让级联删除慢两百多倍，这些结论换机器也大体成立，具体毫秒数会变。
- 几个容易踩的坑：BRIN 只适合按顺序写入的列（时间戳、自增 ID），数据打乱了就没用；索引仅扫描不是真的不碰表，没 VACUUM 过的页面照样要回表查可见性；UNLOGGED 表崩了就清空、也不参与复制，别拿它存重要数据。
- 和华为云的结合点：GaussDB 同样支持部分索引、覆盖索引这类思路，文中的实测方法可以直接复用；华为云数据库的慢 SQL 诊断、索引推荐能力，能帮你把“凭感觉加索引”换成“按数据说话”。
相关阅读（同方向旧文，纯文本链接）： - AI 智能体直接改数据库，约束谁来守？PostgreSQL 约束索引让跨表校验提速最高 2.41 倍：https://bbs.huaweicloud.com/blogs/829a4a73dede49a0b89ffd0a1f9bff3e - 学习型基数估计如何在动态数据库里落地：SWatcher+ 的选择性监控与主动纠偏：https://bbs.huaweicloud.com/blogs/e6f5085214054f46bb2c54e52ee9fd20
原文：Thirteen small measurements on PostgreSQL 18，Hollis Varden，Explain, Measured，2026-11-08。原文链接：https://explainmeasured.com/posts/thirteen-measurements-pg18/
- 点赞
- 收藏
- 关注作者
评论（0）