数据库基础体系 · 第 23/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

PostgreSQL 索引与优化器：B-tree、GIN、GiST、BRIN 和执行计划

在 PostgreSQL 中，“加了索引却没有变快”通常不是索引失效，而是以下某一环的结果：

1. 查询条件不符合该索引访问方法的搜索能力；
2. 优化器估算选择性或代价后，认为顺序扫描更便宜；
3. 索引能够定位候选行，但还需要回表检查大量数据；
4. 表、索引或可见性信息没有及时维护；
5. 查询写法、类型转换或表达式与索引定义不匹配。

要理解这些现象，需要同时掌握三层概念：

• 索引访问方法：B-tree、GIN、GiST、BRIN 如何组织和搜索数据；
• MVCC 与存储状态：索引找到的元组是否对当前事务可见，是否需要访问堆表；
• 优化器与执行计划：PostgreSQL 为什么选择某一种扫描方式，以及如何验证估算是否正确。

---

一、索引在 PostgreSQL 执行过程中的位置

一条简单查询可以抽象为：

SELECT *
FROM orders
WHERE customer_id = 42;

数据库需要完成几件事：

1. 找到满足 customer_id = 42 的行；
2. 判断这些行对当前事务是否可见；
3. 读取查询所需的列；
4. 生成结果。

没有索引时，典型路径是顺序扫描：

读取表的每个数据页
  -> 检查每一行
  -> 判断 customer_id = 42
  -> 进行 MVCC 可见性判断
  -> 返回满足条件的行

有索引时，典型路径是：

搜索索引
  -> 得到一个或多个表行定位信息（TID）
  -> 访问堆表中的对应页面
  -> 做 MVCC 可见性判断
  -> 再检查剩余条件
  -> 返回结果

PostgreSQL 的表通常称为 heap，索引中保存的表行位置是 TID（tuple identifier），通常由数据页号和页内行号组成。索引并不直接替代表数据；大多数索引扫描仍需要根据 TID 回到堆表读取元组。

这解释了一个重要事实：

索引扫描不是“只读取索引”。如果命中的行分布在大量不同的数据页中，随机回表成本可能超过顺序扫描。

1. MVCC 对索引扫描的影响

PostgreSQL 使用 MVCC。一个表行可能有多个版本，而索引通常不能仅凭索引键判断某个版本对当前事务是否可见。因此，索引扫描找到候选元组后，执行器仍可能需要访问堆表并进行可见性检查。

当一个堆页面中的所有元组都对所有当前事务可见时，页面对应的 visibility map 位可以被设置。此时，如果查询所需列全部包含在索引中，PostgreSQL 可能执行 Index Only Scan，只访问索引，并通过 visibility map 判断是否需要回表。

例如：

CREATE TABLE users (
    id          bigint PRIMARY KEY,
    email       text NOT NULL,
    status      text NOT NULL,
    created_at  timestamptz NOT NULL
);

CREATE INDEX users_status_created_idx
    ON users (status, created_at)
    INCLUDE (id, email);

查询：

SELECT id, email
FROM users
WHERE status = 'active'
ORDER BY created_at;

这个查询的过滤列和排序列在索引键中，返回列通过 INCLUDE 存储在索引中，因此有机会使用 Index Only Scan。但“有机会”不是保证：

• 相关堆页面可能尚未被 Vacuum 标记为全可见；
• 表刚刚发生了大量更新；
• 优化器估计回表更便宜；
• 查询还需要索引中没有的列。

因此，INCLUDE 不是无条件的“覆盖索引”保证，而是为索引仅扫描提供数据基础。

---

二、B-tree：通用有序索引

1. B-tree 的搜索模型

PostgreSQL 默认索引方法是 B-tree。它维护键的有序结构，适合以下操作：

• 等值：=
• 范围：<、<=、>、>=
• 排序：ORDER BY
• 前缀匹配的一部分场景，例如 LIKE 'abc%'
• IS NULL、IS NOT NULL 等可由操作符类支持的条件

例如：

CREATE INDEX orders_customer_created_idx
    ON orders (customer_id, created_at);

查询：

SELECT *
FROM orders
WHERE customer_id = 42
  AND created_at >= TIMESTAMPTZ '2025-01-01'
  AND created_at <  TIMESTAMPTZ '2025-02-01'
ORDER BY created_at;

B-tree 可以先定位 customer_id = 42 的索引范围，再在该范围内定位 created_at 的时间区间，并且索引顺序与 ORDER BY created_at 兼容。

从逻辑上看，若索引键按：

(customer_id, created_at)

排序，则比较规则是：

1. 先比较 customer_id；
2. 只有当 customer_id 相同，才比较 created_at。

所以多列 B-tree 的关键规则是左侧前缀。

2. 多列索引的左侧前缀

索引：

CREATE INDEX events_tenant_time_idx
    ON events (tenant_id, occurred_at);

下面的条件都可能有效利用索引：

WHERE tenant_id = 10

WHERE tenant_id = 10
  AND occurred_at >= now() - interval '1 day'

WHERE tenant_id = 10
ORDER BY occurred_at

而只有时间条件：

WHERE occurred_at >= now() - interval '1 day'

通常不能像单列 occurred_at 索引那样有效地缩小 B-tree 搜索范围。优化器仍可能选择扫描整个索引，或进行跳跃式扫描，但不能把它理解为普通意义上的“直接按第二列定位”。

一个更形式化的描述是：

假设索引键为：

$$K = (k_1, k_2, \ldots, k_n)$$

如果查询对 k_1 给出等值条件，那么可以在相同 k_1 的范围内继续利用 k_2。如果 k_1 没有约束，k_2 的约束通常不能单独形成一个连续的索引区间。

3. 等值列、范围列和排序列

考虑查询：

SELECT *
FROM logs
WHERE service = 'api'
  AND created_at >= now() - interval '1 hour'
ORDER BY created_at
LIMIT 100;

常见候选索引是：

CREATE INDEX logs_service_created_idx
    ON logs (service, created_at);

原因是：

• service 是等值条件，先缩小范围；
• created_at 是范围条件，同时可以支持时间顺序；
• LIMIT 100 使“按索引顺序尽快找到前 100 行”具有价值。

但列顺序不是机械规则。若查询主要是：

WHERE created_at >= ...
  AND service = ...

SQL 书写顺序不决定索引顺序；真正重要的是数据分布、选择性、排序需求和其他查询模式。

4. B-tree 的反例：低选择性条件

假设表有一亿行，只有两种状态：

status IN ('active', 'inactive')

查询：

SELECT *
FROM users
WHERE status = 'active';

如果 active 占 70%，使用索引需要：

1. 扫描大量索引项；
2. 回表读取大量数据页；
3. 进行可见性检查。

顺序扫描只需按物理顺序读取整张表，可能更便宜。因此，即使存在：

CREATE INDEX users_status_idx ON users (status);

也不应期待这个查询必然使用索引。

5. NULL、表达式和部分索引

B-tree 可以处理 NULL，但排序位置和 NULL 排序规则需要注意。默认排序中，升序通常将 NULL 放在最后；可以显式指定：

CREATE INDEX users_last_login_idx
    ON users (last_login DESC NULLS LAST);

如果查询对列使用了函数：

WHERE lower(email) = 'alice@example.com'

普通索引：

CREATE INDEX users_email_idx ON users (email);

并不等价于对 lower(email) 建索引。可以建立表达式索引：

CREATE INDEX users_lower_email_idx
    ON users (lower(email));

部分索引只索引满足谓词的行：

CREATE INDEX tasks_open_idx
    ON tasks (priority, created_at)
    WHERE status = 'open';

它适合“查询经常只关注一小部分稳定子集”的场景。查询条件必须能够被优化器证明蕴含部分索引谓词。例如：

WHERE status = 'open'
  AND priority >= 10

可以匹配上面的索引。若通过复杂参数、函数或不透明表达式隐藏了 status = 'open'，优化器可能无法证明这一点。

6. B-tree 不适合的情况

B-tree 不是通用容器索引。以下情况通常需要其他方法：

• 数组包含关系；
• JSONB 内部键值和包含关系；
• 全文检索；
• 几何对象的相交、包含、邻近；
• 在物理顺序高度相关的大表上做粗粒度范围过滤。

这些场景分别经常涉及 GIN、GiST 或 BRIN。

---

三、GIN：倒排索引与复合值搜索

1. GIN 的基本结构

GIN（Generalized Inverted Index，广义倒排索引）不是“一个复合值对应一个索引键”，而是把一个值拆成多个可搜索的元素：

文档                         索引元素
------------------------------------------------
"postgresql database"        postgresql, database
数组 {1, 3, 5}               1, 3, 5
JSONB {"a":1, "b":2}         由操作符类定义的多个键/路径/值

索引逻辑近似于：

元素 -> 包含该元素的行

因此 GIN 特别适合：

• 数组包含查询；
• jsonb 包含和键存在查询；
• 全文检索中的词项查询。

GIN 的核心代价是：一个表行可能产生很多索引项，写入、更新和 Vacuum 成本通常高于简单 B-tree。

2. JSONB 示例

建立测试表：

CREATE TABLE documents (
    id      bigserial PRIMARY KEY,
    payload jsonb NOT NULL
);

CREATE INDEX documents_payload_gin_idx
    ON documents USING GIN (payload);

插入数据：

INSERT INTO documents (payload) VALUES
    ('{"type":"order","status":"paid","tags":["vip","online"]}'),
    ('{"type":"order","status":"pending","tags":["online"]}'),
    ('{"type":"ticket","status":"open","tags":["vip"]}');

包含查询：

SELECT id, payload
FROM documents
WHERE payload @> '{"type":"order","status":"paid"}';

@> 表示左侧 JSONB 是否包含右侧 JSONB。默认 jsonb_ops 操作符类支持较宽的 JSONB 操作集合。

对于主要使用包含查询的场景，可以考虑：

CREATE INDEX documents_payload_path_gin_idx
    ON documents USING GIN (payload jsonb_path_ops);

jsonb_path_ops 通常建立更紧凑、针对包含路径优化的索引，但它支持的操作范围比默认 jsonb_ops 更窄。不能只根据索引名称判断优劣，必须根据实际运算符和查询集合选择操作符类。

例如，以下查询不是同一类问题：

WHERE payload @> '{"status":"paid"}'

和：

WHERE payload ? 'status'

后者是键存在性检查。若业务大量使用不同 JSONB 运算符，就不能简单地用 jsonb_path_ops 替代默认操作符类。

3. JSONB 的反例：对标量字段无条件建立整列 GIN

如果查询总是：

WHERE payload->>'user_id' = '42'

对整个 payload 建 GIN 不一定是最合适方案。更明确的做法通常是提取字段并建立 B-tree 表达式索引：

CREATE INDEX documents_user_id_idx
    ON documents ((payload->>'user_id'));

如果值是数字，应保持类型一致：

CREATE INDEX documents_user_id_bigint_idx
    ON documents (((payload->>'user_id')::bigint));

查询也要使用同样的表达式：

WHERE (payload->>'user_id')::bigint = 42

原因是这个查询本质上是一个标量等值查找，B-tree 能直接提供有序定位；GIN 的优势在于一个值被拆成多个元素后进行集合匹配，而不是所有 JSONB 访问都更快。

4. GIN 的写入路径与 pending list

GIN 为降低单行写入时立即修改复杂倒排结构的成本，通常可以使用 pending list。参数 fastupdate 控制相关行为，默认通常开启。写入先积累在 pending list，之后在查询、维护或达到阈值时合并到主索引结构。

这会带来两个相反的现象：

• 大量写入时，单次写入可能更快；
• pending list 合并时，可能出现突发 I/O 或查询延迟。

可以通过索引存储参数控制：

CREATE INDEX documents_payload_gin_idx
    ON documents USING GIN (payload)
    WITH (fastupdate = off);

是否关闭不能凭经验决定。高写入、高查询并发和批量导入场景的最佳选择可能不同，应结合索引大小、延迟、Vacuum 和实际执行计划验证。

5. GIN 的回查与 recheck

GIN 找到的是“包含某些索引元素的候选行”。对于某些运算符和索引结构，索引结果可能不是最终精确结果，执行器需要回表再次检查条件。执行计划中可能看到：

Rows Removed by Index Recheck

这不是错误，而是索引访问方法允许返回候选集后再过滤的表现。代价在于候选集越大，回表和重检查越多。

---

四、GiST：可扩展的搜索树框架

1. GiST 不等于某一种固定数据结构

GiST（Generalized Search Tree，广义搜索树）是一个可扩展索引框架。它定义了树形组织和一致性检查等接口，但具体“如何比较”和“如何分组”由操作符类决定。

GiST 常见用途包括：

• 范围类型；
• 几何类型；
• PostGIS 空间对象；
• inet 等支持相应操作符类的类型；
• 排序距离相关操作。

因此，“GiST 支持范围查询”并不是仅因为 GiST 这个名字，而是因为对应数据类型提供了合适的 GiST 操作符类。

2. 范围类型示例

CREATE TABLE reservations (
    id         bigserial PRIMARY KEY,
    room_id    integer NOT NULL,
    booked     tstzrange NOT NULL
);

CREATE INDEX reservations_booked_gist_idx
    ON reservations USING GIST (booked);

查询与某个时间段相交：

SELECT *
FROM reservations
WHERE booked && tstzrange(
    TIMESTAMPTZ '2025-06-01 10:00+00',
    TIMESTAMPTZ '2025-06-01 11:00+00',
    '[)'
);

&& 表示两个范围相交。这里使用半开区间 [)，表示包含开始时间、不包含结束时间，可以避免相邻时间段在边界处重叠。

如果还要按 room_id 限制：

CREATE INDEX reservations_room_booked_gist_idx
    ON reservations USING GIST (room_id, booked);

多列 GiST 与 B-tree 多列索引的直觉不同。GiST 的每一列都通过操作符类参与索引谓词，不能直接套用“第一列等值、第二列范围”的 B-tree 左前缀规则。实际效果取决于各列的 GiST 操作符类、数据分布和查询条件。

3. 排他约束：索引参与约束检查

范围索引不仅能加速查询，还可以服务于排他约束：

CREATE TABLE room_bookings (
    room_id integer NOT NULL,
    booked  tstzrange NOT NULL,
    EXCLUDE USING GIST (
        room_id WITH =,
        booked  WITH &&
    )
);

其约束语义是：任意两行不能同时满足：

room_id 相等
并且 booked 范围相交

这比“先查询是否冲突，再插入”的应用层检查更可靠，因为应用层检查在并发事务下会有竞态：

事务 A：查询，没有冲突
事务 B：查询，没有冲突
事务 A：插入
事务 B：插入

排他约束由数据库在约束语义和并发控制下执行检查。使用它仍需考虑事务隔离级别、冲突等待和异常处理；插入冲突时，应用应正确处理数据库异常，而不是假设插入一定成功。

4. GiST 的近似边界与 recheck

GiST 节点通常用一个谓词或边界描述子树中的值。例如空间索引可以用包围盒描述一组几何对象。两个包围盒相交，并不意味着其中的实际几何对象一定相交，因此搜索过程可能得到候选项，随后必须进行精确检查。

执行计划中可能看到：

Index Cond: ...
Rows Removed by Index Recheck: ...

这体现了 GiST 的重要边界：

GiST 索引条件负责缩小搜索空间，最终正确性仍由精确运算符检查保证。

如果数据边界重叠严重，索引选择性会下降，候选集和 recheck 数量会上升。

---

五、BRIN：按数据页范围保存摘要

1. BRIN 的思想

BRIN（Block Range Index，块范围索引）不为每一行保存索引项，而是为连续的一组表数据页保存摘要。

以默认 minmax 方式为例，一个范围摘要可能是：

数据页范围 0～127：
    created_at 最小值 = 2025-01-01 00:00
    created_at 最大值 = 2025-01-01 00:17

查询：

WHERE created_at >= '2025-06-01'

如果某个页范围的最大值小于 2025-06-01，该页范围必然没有匹配行，可以跳过。若摘要范围与查询条件相交，就不能排除，必须读取该页范围并逐行过滤。

BRIN 依赖的是物理存储顺序与列值之间的相关性，而不是每行精确定位。

2. 适合 BRIN 的数据

典型适用场景：

• 按时间持续追加的日志表；
• 自增 ID 大致按插入顺序增长；
• 大表中查询范围较宽，但数据物理顺序与过滤列高度相关；
• 希望索引尺寸很小、维护成本较低。

示例：

CREATE TABLE access_logs (
    id          bigint GENERATED ALWAYS AS IDENTITY,
    occurred_at timestamptz NOT NULL,
    service     text NOT NULL,
    message     text NOT NULL
);

CREATE INDEX access_logs_occurred_brin_idx
    ON access_logs USING BRIN (occurred_at);

如果插入顺序大致按 occurred_at 增长，BRIN 可以排除大量不相关页范围。

3. BRIN 的反例：随机写入的时间列

若数据按随机时间写入：

页面 1：2024、2025、2023、2025、2022
页面 2：2021、2025、2020、2024、2025
页面 3：2022、2023、2025、2021、2024

每个页范围的最小值和最大值会覆盖很宽的区间。查询任意时间段时，大多数页范围都无法被排除，BRIN 退化为“读取大量页面后过滤”。

因此，BRIN 索引小，不意味着查询必然快。它的性能关键不是“索引大小”，而是摘要能否有效排除页面。

4. pages_per_range 与摘要维护

BRIN 使用 pages_per_range 将表页面分组。范围越大：

• 索引更小；
• 每个摘要更粗；
• 排除能力可能下降。

范围越小：

• 索引更大；
• 摘要更精确；
• 维护和扫描成本增加。

可以在创建时指定：

CREATE INDEX access_logs_occurred_brin_idx
    ON access_logs USING BRIN (occurred_at)
    WITH (pages_per_range = 32);

新插入数据可能落入尚未建立或尚未更新的范围摘要。可以手动重新汇总：

SELECT brin_summarize_new_values('access_logs_occurred_brin_idx');

该函数只适用于对应 BRIN 索引，并且执行时间、锁和 I/O 影响应在业务环境中评估。BRIN 不是实时统计服务；它依赖摘要状态。

5. BRIN 的 lossy 结果

由于 BRIN 保存的是页范围摘要，而不是行级定位，执行计划中常见：

Bitmap Heap Scan on access_logs
  Recheck Cond: ...

这表示索引只确认“这个页范围可能有匹配行”，执行器还要访问页面并重新检查行。对于 BRIN，这是设计特征，不是异常。

---

六、操作符类：索引方法与运算符之间的契约

创建索引时，PostgreSQL 不仅需要知道索引方法，还需要知道列使用哪个操作符类（operator class）。

操作符类定义了：

• 哪些运算符可以用于搜索；
• 数据如何映射到索引结构；
• 索引方法如何判断候选匹配；
• 某种数据类型在该索引方法下的语义。

例如：

CREATE INDEX documents_payload_idx
    ON documents USING GIN (payload jsonb_path_ops);

这里的 jsonb_path_ops 就是操作符类。它不是装饰性配置，而是决定 GIN 能高效支持哪些 JSONB 运算。

诊断某个类型有哪些操作符类，可以查询系统目录：

SELECT
    opcname,
    amname,
    format_type(opcintype, NULL) AS input_type
FROM pg_opclass c
JOIN pg_am am ON am.oid = c.opcmethod
WHERE format_type(opcintype, NULL) IN ('jsonb', 'text', 'integer');

实际输出会因版本和已安装扩展而不同。不能把一个操作符类的能力推断到另一个操作符类上。

---

七、优化器如何选择执行计划

1. 逻辑计划与物理计划

优化器先理解查询的关系代数语义，再生成物理执行方案。例如：

SELECT *
FROM orders
WHERE customer_id = 42
  AND created_at >= '2025-01-01';

可能的物理方案包括：

• 顺序扫描；
• B-tree 索引扫描；
• Bitmap Index Scan + Bitmap Heap Scan；
• 并行顺序扫描；
• 多个索引的 BitmapAnd；
• 连接时使用 Nested Loop、Hash Join 或 Merge Join。

优化器估算每个方案的成本，选择估计成本最低者。这个“成本”不是实际毫秒数，而是由页面读取、CPU 运算、并行等因素构成的相对模型。

2. 选择性与行数估算

设表有 $N$ 行，谓词选择性为 $s$，则估计匹配行数近似为：

$$R = N \times s$$

其中：

• $N$：优化器认为表有多少行；
• $s$：满足条件的比例；
• $R$：估计输出行数。

如果优化器错误地认为：

实际：status = 'active' 匹配 80%
估计：status = 'active' 匹配 1%

它可能选择索引扫描。实际执行时需要回表读取绝大多数数据，最终计划会明显慢于顺序扫描。

统计信息主要来自：

ANALYZE orders;

也可以提高某个列的统计目标：

ALTER TABLE orders
    ALTER COLUMN customer_id SET STATISTICS 1000;

ANALYZE orders;

更高统计目标可能增加分析时间和系统目录信息，但能让直方图、最常见值等统计信息更细。它不是无条件提高性能的开关。

3. 相关列与独立性假设

如果查询有两个条件：

WHERE country = 'CN'
  AND city = 'Beijing'

一个简单估算可能近似认为两个条件独立：

$$P(country='CN' \land city='Beijing') \approx P(country='CN') \times P(city='Beijing')$$

但现实中 city 和 country 显然相关。独立性假设会造成错误估算。

可以建立扩展统计信息：

CREATE STATISTICS users_country_city_stats
    (dependencies, ndistinct, mcv)
    ON country, city
FROM users;

ANALYZE users;

这里：

• dependencies 描述列之间的函数依赖或相关性；
• ndistinct 帮助估计组合不同值数量；
• mcv 保存多列常见值组合。

扩展统计信息改善的是优化器估算，不是直接创建索引。它也不会自动解决所有相关性问题。

4. 成本模型与扫描类型

常见成本参数包括：

• seq_page_cost：顺序读取页面的相对成本；
• random_page_cost：随机读取页面的相对成本；
• cpu_tuple_cost：处理一行的成本；
• cpu_index_tuple_cost：处理一个索引项的成本；
• effective_cache_size：优化器对操作系统和 PostgreSQL 可用缓存规模的估计，不是实际分配的内存。

这些参数影响计划选择，但不应通过“为了让某查询走索引而随意调低 random_page_cost”来掩盖统计信息错误或数据分布问题。

5. Index Scan、Bitmap Heap Scan 和顺序扫描

Index Scan

典型流程：

索引逐项找到 TID
  -> 逐个访问堆表页面
  -> 返回行

适合匹配行较少、且按索引顺序读取有价值的情况。

Bitmap Heap Scan

典型流程：

Bitmap Index Scan
  -> 收集多个匹配 TID 或页面
  -> 按堆表页面重新排序
  -> Bitmap Heap Scan 批量访问页面
  -> 重新检查条件

它在“匹配行不太少，但又不值得顺序扫描”时经常有优势。多个索引还可以组合：

BitmapAnd
  -> Bitmap Index Scan on index_a
  -> Bitmap Index Scan on index_b

Sequential Scan

顺序读取表页面并逐行过滤。若命中率很高、表较小、缓存友好，或者索引回表代价很高，顺序扫描就是合理方案。

“执行计划没有使用索引”不等于优化器错误。

---

八、执行计划：看懂估算、实际和节点关系

1. 基本命令

查看估算计划：

EXPLAIN
SELECT *
FROM orders
WHERE customer_id = 42;

执行并记录真实运行信息：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT *
FROM orders
WHERE customer_id = 42;

EXPLAIN ANALYZE 会真正执行语句。对于 SELECT 通常没有业务数据修改，但它仍可能产生：

• CPU 和 I/O；
• 锁等待；
• 函数副作用；
• 触发器或其他执行行为。

对 UPDATE、DELETE、INSERT 使用时尤其要小心。生产诊断前应确认事务边界，必要时使用回滚测试：

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'archived'
WHERE created_at < now() - interval '1 year';

ROLLBACK;

这只能回滚事务中的数据修改，但执行期间产生的锁、WAL 和外部副作用不一定都能像数据一样“撤销”。

2. 一个示例计划

以下输出是示意，具体数字和节点会因版本、数据量、缓存和统计信息不同而变化：

Limit  (cost=0.42..8.60 rows=10 width=64)
       (actual time=0.041..0.073 rows=10 loops=1)
  Buffers: shared hit=13
  ->  Index Scan using orders_customer_created_idx on orders
       (cost=0.42..820.00 rows=1000 width=64)
       (actual time=0.040..0.070 rows=10 loops=1)
       Index Cond: ((customer_id = 42)
                   AND (created_at >= '2025-01-01'::timestamptz))
Planning Time: 0.300 ms
Execution Time: 0.100 ms

阅读方式：

• cost=0.42..820.00
  • 第一个成本是产生第一行的启动成本；
  • 第二个成本是产生全部估计结果的总成本；
• rows=1000
  • 优化器估计输出 1000 行；
• actual ... rows=10
  • 实际输出 10 行；
• loops=1
  • 节点执行一次；
• Index Cond
  • 真正用于索引定位的条件；
• Filter
  • 找到候选行后再过滤的条件；
• Buffers: shared hit=13
  • 访问了 13 个共享缓冲区页面，且都命中内存；
• Planning Time
  • 规划时间；
• Execution Time
  • 执行时间，不包括客户端网络处理的完整端到端延迟。

如果节点嵌套在循环中，不能只看节点单次 actual time，还要结合 loops 理解总成本。一个看似只耗时 0.1 毫秒的节点，如果执行了几十万次，累计成本可能很高。

3. Index Cond 与 Filter 的区别

例如：

CREATE INDEX orders_customer_idx ON orders(customer_id);

查询：

SELECT *
FROM orders
WHERE customer_id = 42
  AND lower(status) = 'open';

可能出现：

Index Cond: (customer_id = 42)
Filter: (lower(status) = 'open')

这说明索引只负责根据 customer_id 定位，status 条件是在取出候选行后执行的。若 status 过滤掉大量行，可能需要：

• 建立合适的复合索引；
• 建立表达式索引；
• 使用部分索引；
• 或接受顺序扫描更便宜。

4. 估算误差的诊断

重点比较：

估计 rows
实际 rows

例如：

Index Scan ... rows=100
             actual ... rows=100000

这表明估算误差达到三个数量级，后续连接顺序和连接算法都可能因此错误。

诊断顺序通常是：

1. 确认统计信息是否更新：

   ANALYZE orders;
   

2. 检查谓词是否包含类型转换、函数或隐式转换；

3. 检查列是否有明显数据倾斜；

4. 检查多列相关性，必要时创建扩展统计信息；

5. 检查表是否刚刚大量插入、更新或删除；

6. 再考虑成本参数和查询结构。

不要一看到慢查询就执行：

SET enable_seqscan = off;

这类参数可以用于诊断“如果强制不用顺序扫描会怎样”，但不是生产修复方案。它可能迫使优化器选择更差的计划，也不能修复错误统计信息。

---

九、排序、LIMIT 与索引顺序

索引不仅可以过滤，还可以提供有序输出。

CREATE INDEX messages_user_created_idx
    ON messages (user_id, created_at DESC);

查询：

SELECT id, created_at, body
FROM messages
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

如果索引顺序匹配，执行器可以：

定位 user_id = 42
  -> 从最新 created_at 开始读取
  -> 取够 20 行后停止

这时索引的价值不仅是减少过滤行，还避免了对全部候选行排序。

反例：

SELECT id, created_at, body
FROM messages
WHERE user_id = 42
ORDER BY body
LIMIT 20;

若索引没有 body 的合适顺序，仍可能需要读取大量候选行并排序。LIMIT 并不自动意味着低成本；只有执行器能较早得到正确顺序的结果时，LIMIT 才能显著降低工作量。

---

十、索引交集、覆盖索引和写入代价

1. 多个单列索引不等价于一个复合索引

假设有：

CREATE INDEX users_country_idx ON users(country);
CREATE INDEX users_status_idx  ON users(status);

查询：

WHERE country = 'CN'
  AND status = 'active'

优化器可能使用 BitmapAnd 合并两个索引，也可能选择其中一个索引后过滤，或者顺序扫描。

但这不等价于：

CREATE INDEX users_country_status_idx
    ON users(country, status);

复合索引能够直接按组合键组织数据，还可能支持排序和更少的索引访问步骤。哪一种更好取决于查询组合和写入模式。

2. INCLUDE 与索引键的区别

CREATE INDEX products_category_price_idx
    ON products (category, price)
    INCLUDE (name, description);

category 和 price 是搜索和排序键；name、description 是附加存储列，主要服务于 Index Only Scan，不能像键列一样用于普通排序定位。

将大字段放进 INCLUDE 会增加索引大小、写入和 Vacuum 成本，甚至使索引页膨胀。它不是“把查询返回的所有列都放进去”的通用规则。

3. 索引对写入的影响

每次插入、删除或更新可能需要维护多个索引：

• 插入增加索引项；
• 删除和更新产生索引维护工作；
• 更新索引键可能需要新增索引项；
• 即使更新的列不在索引键中，MVCC 和 HOT 更新条件也会影响存储行为；
• GIN 等复杂索引的写放大通常更加明显。

因此，索引优化必须同时看读和写。一个只服务于低频查询的索引，可能不值得长期承担写入、存储和维护成本。

---

十一、MVCC、Vacuum 与索引诊断

1. 为什么删除行不会立即消失

在 PostgreSQL 中，DELETE 通常只是创建一个删除标记；旧版本仍可能对其他事务可见。UPDATE 也通常创建新版本。旧版本在不再被任何事务需要后，才可由 Vacuum 回收空间。

这会影响索引优化：

• 表和索引可能包含大量无效版本；
• 索引扫描可能遇到更多候选项；
• visibility map 位可能因为更新而被清除；
• Index Only Scan 可能退化为频繁回表；
• 长事务会阻止旧版本回收。

2. 相关诊断命令

查看表和索引大小：

SELECT
    relname,
    pg_size_pretty(pg_relation_size(oid)) AS relation_size,
    pg_size_pretty(pg_indexes_size(oid))  AS indexes_size
FROM pg_class
WHERE relname = 'orders';

查看统计信息：

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    last_analyze,
    last_autoanalyze,
    last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

查看索引使用统计：

SELECT
    indexrelname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders';

这些统计是累计统计，不是单次查询结果。idx_scan 很低不必然代表索引无用，因为可能是：

• 统计周期刚重置；
• 查询刚上线；
• 查询使用了 Bitmap 路径，解释方式需要结合计划；
• 索引服务于约束而非读取；
• 业务低峰尚未覆盖。

3. 膨胀与重建

普通 VACUUM 主要帮助回收可复用空间、更新可见性信息并避免事务 ID 风险；它不等于把索引物理文件压缩成最小状态。

重建索引可以使用：

REINDEX INDEX CONCURRENTLY orders_customer_created_idx;

该命令的可用性、锁行为和限制应以目标 PostgreSQL 版本文档为准。CONCURRENTLY 通常减少阻塞正常读写的影响，但会产生额外 I/O、构建时间和临时空间需求，也不能在所有事务上下文中随意使用。

删除无用索引前，应先确认：

• 是否被约束依赖；
• 是否只在低频关键任务中使用；
• 统计周期是否足够长；
• 是否存在分区级索引或继承关系；
• 是否有业务查询尚未覆盖。

---

十二、一个完整的实验流程

下面用订单表演示“建立索引—查看计划—验证估算”的闭环。

1. 建表和数据

CREATE TABLE demo_orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL,
    status      text NOT NULL,
    created_at  timestamptz NOT NULL,
    amount      numeric(12,2) NOT NULL
);

INSERT INTO demo_orders (customer_id, status, created_at, amount)
SELECT
    (random() * 99999)::bigint + 1,
    CASE
        WHEN random() < 0.9 THEN 'paid'
        ELSE 'pending'
    END,
    TIMESTAMPTZ '2025-01-01'
        + random() * interval '180 days',
    round((random() * 10000)::numeric, 2)
FROM generate_series(1, 1000000);

ANALYZE demo_orders;

这里生成约一百万行，status = 'paid' 的比例约为 90%，但随机值和实际比例仍有统计波动。

2. 比较顺序扫描和索引扫描

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM demo_orders
WHERE status = 'paid';

即使建立：

CREATE INDEX demo_orders_status_idx
    ON demo_orders(status);

ANALYZE demo_orders;

优化器仍可能选择顺序扫描，因为查询返回了绝大多数行。

再建立客户索引：

CREATE INDEX demo_orders_customer_created_idx
    ON demo_orders(customer_id, created_at);

ANALYZE demo_orders;

查询一个具体客户：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, created_at, amount
FROM demo_orders
WHERE customer_id = 12345
  AND created_at >= TIMESTAMPTZ '2025-03-01'
  AND created_at <  TIMESTAMPTZ '2025-04-01'
ORDER BY created_at;

预期观察重点不是某个固定耗时，而是：

• 是否出现 Index Scan 或 Bitmap Heap Scan；
• Index Cond 是否包含 customer_id 和时间条件；
• 估计行数与实际行数是否接近；
• Buffers 中 hit/read 的比例；
• 是否还发生了额外 Sort；
• 是否由于返回 amount 而需要回表。

3. 构造估算错误的检查

SELECT
    most_common_vals,
    most_common_freqs,
    histogram_bounds
FROM pg_stats
WHERE tablename = 'demo_orders'
  AND attname = 'status';

如果实际数据已经发生严重倾斜，而 ANALYZE 没有及时运行，计划中的估算可能过时。重新分析后再次执行 EXPLAIN (ANALYZE, BUFFERS)，观察 rows 与 actual rows 是否改善。

---

十三、如何根据访问模式选择索引

可以用“查询需要什么证明”来选择访问方法，而不是先记忆索引名称。

B-tree

查询需要证明：

键值处于某个有序区间

适合：

=, <, <=, >, >=
ORDER BY
LIMIT

GIN

查询需要证明：

一个复合值包含某些元素

适合：

数组元素
JSONB 包含
全文检索词项

GiST

查询需要证明：

两个对象满足某种关系

例如：

范围相交
几何对象相交
距离或包含关系

具体能力由操作符类决定。

BRIN

查询需要证明：

某个数据页范围的摘要不可能满足条件

适合物理顺序和过滤列高度相关的大表。

---

十四、常见误解与失败表现

误解一：每个 WHERE 列都应建索引

错误原因是忽略了：

• 选择性；
• 查询频率；
• 组合条件；
• 排序和 LIMIT；
• 写入维护成本；
• 数据物理顺序。

一个低选择性列的单列 B-tree，可能对主要查询没有帮助。

误解二：索引存在，优化器就必须使用

优化器比较的是候选计划成本。若返回大量行、表很小、缓存命中良好，顺序扫描可能更优。

误解三：看到 Index Scan 就说明查询快

索引扫描可能返回大量候选行并频繁随机回表。必须看：

actual rows
Buffers
loops
Rows Removed by Filter
Rows Removed by Index Recheck

误解四：看到 Seq Scan 就说明索引失效

顺序扫描可能是正确选择。应先判断：

• 查询是否返回大部分表；
• 表是否很小；
• 条件是否低选择性；
• 统计信息是否准确；
• 是否存在类型或表达式不匹配。

误解五：BRIN 适用于所有大表

BRIN 适合“物理顺序相关”的大表，不适合值在页面中高度随机分布的表。

误解六：GIN 适用于所有 JSONB 查询

JSONB 的访问方式必须与操作符类匹配。对固定标量字段的等值查询，表达式 B-tree 往往更直接；对复杂包含关系，GIN 才体现其倒排优势。

误解七：EXPLAIN ANALYZE 只是查看计划

它会执行语句。写操作、锁等待、函数副作用和资源消耗都必须纳入风险评估。

---

十五、最终诊断思路

遇到一个慢查询，可以按以下因果链检查：

查询谓词
  -> 是否与索引操作符类匹配
  -> 能否形成有效搜索条件
  -> 估计选择性是否正确
  -> 选择的扫描节点是否合理
  -> 回表、排序、重检查是否昂贵
  -> MVCC 和 Vacuum 是否增加额外成本
  -> 读性能收益是否值得写入维护代价

具体执行时：

1. 用 EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) 获取真实计划；
2. 比较估计行数和实际行数；
3. 区分 Index Cond、Filter 和 Recheck Cond；
4. 检查表统计信息和多列相关性；
5. 根据访问模式选择 B-tree、GIN、GiST 或 BRIN；
6. 重新分析后再次验证，而不是只观察一次计划；
7. 在真实数据分布、缓存状态和并发负载下评估；
8. 同时检查 Vacuum、可见性映射、索引膨胀和写入代价。

索引是执行器可用的一组访问路径，优化器负责在这些路径之间做成本决策。B-tree 提供有序键查找，GIN 提供复合值的倒排搜索，GiST 提供可扩展的关系搜索框架，BRIN 则用页范围摘要换取极小的索引规模。只有把这些访问方法的搜索语义、MVCC 可见性和执行计划中的实际行为联系起来，才能解释“为什么这个索引有效”“为什么那个索引没有被使用”，并做出可验证的优化选择。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：PostgreSQL MVCC 与 Vacuum：Tuple 可见性、膨胀、冻结和诊断
• 下一篇：PostgreSQL 复制与高可用：流复制、复制槽、PITR 和故障切换
• 延伸：PostgreSQL JSONB 与扩展生态：全文检索、分区、FDW 和扩展边界

官方资料

• PostgreSQL Current Documentation
• PostgreSQL Internals

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。