PostgreSQL笔记34：索引优化策略全景解析——从B-tree到HOT的核心原理与实践

纲要

• B-tree 索引
  • 适用场景与操作符
  • 索引项大小限制（约三分之一页）
• Hash 索引
  • 仅支持等值查询
  • WAL日志记录问题（PostgreSQL 10以下版本）
• BRIN 索引（Block Range Index）
  • 块范围摘要信息（minmax 操作符类）
  • 适用时序数据场景
  • 索引体积优势
• GIN 索引（Generalized Inverted Index）
  • 倒排索引原理与全文检索
  • 数组、JSONB等复合类型支持
  • 更新代价较高
• GiST 索引（Generalized Search Tree）
  • 地理空间数据与最近邻搜索
  • 有损索引与回表确认
• 函数索引（Expression Index）
  • 索引表达式而非列值
  • 统计信息独立存储
• 部分索引（Partial Index）
  • 条件索引缩小索引体积
• 多字段索引（复合索引）
  • 最左前缀匹配原则
• 唯一索引（Unique Index）
  • 唯一性约束实现机制
  • 死元组与表膨胀风险
• correlation 相关度
  • 逻辑顺序与物理顺序的关联程度
  • 对索引扫描代价的影响
• Index-Only Scan 与 Visibility Map
  • VM文件的作用与 all-visible 标记
  • 回表确认的必要性
• HOT（Heap-Only Tuple）
  • 非索引列更新时的优化机制
  • 条件：同页存储 + 不更新索引键

B-tree 索引

PostgreSQL 默认创建的索引类型即为 B-tree 索引，通过 CREATE INDEX 命令（不指定 USING 子句）或显式指定 USING BTREE 均可创建。B-tree 索引适用于可排序数据的等值查询和范围查询，当索引列涉及 <、<=、=、>=、> 等比较运算符时，查询规划器均会考虑使用 B-tree 索引。此外，BETWEEN、IN 以及 IS NULL / IS NOT NULL 条件同样可以利用 B-tree 索引。

B-tree 索引还可用于排序检索（ORDER BY）以及前缀匹配的模式查询（如 col LIKE 'foo%'）。

索引项大小限制：B-tree 索引的索引项不能超过大约一页的三分之一（在应用 TOAST 压缩后计算）。若索引字段长度超过该限制（例如在 8KB 页大小的数据库中，单个索引项超过约 2.7KB），创建索引时将报错。此时需考虑使用其他索引类型或对字段进行哈希处理。

-- 创建 B-tree 索引（默认）
CREATE INDEX idx_test_id ON test (id);

-- 显式指定 B-tree
CREATE INDEX idx_test_name ON test USING BTREE (name);

-- 范围查询示例
EXPLAIN SELECT * FROM test WHERE id BETWEEN 100 AND 200;

Hash 索引

Hash 索引存储从索引列值派生的 32 位哈希码，因此仅支持等值比较（= 运算符）。

WAL 日志问题：在 PostgreSQL 10 之前，Hash 索引操作不进行 WAL 日志记录。这意味着数据库崩溃后，Hash 索引可能需要通过 REINDEX 重建；同时，由于缺少 WAL 日志，Hash 索引也无法复制到备库。PostgreSQL 10 开始对 Hash 索引实现了 WAL 日志记录，但在实际生产环境中，B-tree 索引因其通用性仍是大多数场景的首选。

-- 创建 Hash 索引（注意版本限制）
CREATE INDEX idx_test_hash ON test USING HASH (id);

-- 仅支持等值查询
EXPLAIN SELECT * FROM test WHERE id = 100;

BRIN 索引（Block Range Index）

BRIN 全称为 Block Range Index（块范围索引）。其核心思想是：对于每个块范围（一组物理上相邻的数据页），索引存储该范围内索引列的摘要信息（如最小值和最大值）。查询时，BRIN 索引通过摘要信息快速排除不可能包含匹配数据的块范围，从而大幅减少需要扫描的数据量。

BRIN 索引是有损索引（lossy index）——索引返回的是整个块范围内的所有元组，由查询执行器负责重新检查并过滤不匹配的行。

-- 创建 BRIN 索引，默认 pages_per_range = 128
CREATE INDEX idx_test_brin ON test USING BRIN (created_at);

-- 指定块范围大小（页数），数值越大索引越大但精度越高
CREATE INDEX idx_test_brin_custom ON test USING BRIN (created_at) WITH (pages_per_range = 64);

适用场景：

• 时序数据（仅插入、极少更新删除）
• 索引列与物理存储位置存在自然相关性（如自增 ID、时间戳列）
• 超大规模表（数十亿行级别）

优势：索引体积远小于 B-tree 索引，维护开销低。

摘要触发：BRIN 索引的摘要信息可通过 VACUUM 手动或自动触发，也可通过 brin_summarize_new_values() 和 brin_summarize_range() 函数主动触发。

GIN 索引（Generalized Inverted Index）

GIN 全称为 Generalized Inverted Index（通用倒排索引）。GIN 索引存储（键，倒排列表）对，其中倒排列表是键出现的行 ID 集合。每个键值只存储一次，因此对于同一键多次出现的情况非常紧凑。

GIN 索引内部构建于 B-tree 之上，但键是索引项的元素而非索引项本身。

-- 全文检索 GIN 索引（tsvector 类型）
CREATE INDEX idx_docs_gin ON documents USING GIN (to_tsvector('english', body));

-- 数组类型 GIN 索引
CREATE INDEX idx_tags_gin ON articles USING GIN (tags);

-- JSONB 类型 GIN 索引
CREATE INDEX idx_data_gin ON events USING GIN (payload jsonb_ops);

适用场景：

• 全文检索（tsvector / tsquery）
• 数组元素搜索（@>、&& 等操作符）
• JSONB 文档查询（@>、?、?| 等操作符）
• 多列任意组合查询（如 EAV 模型）

注意事项：GIN 索引的更新代价较高。类比书籍目录——每次数据变更都需要更新倒排列表，因此写入密集型场景需谨慎评估。

GiST 索引（Generalized Search Tree）

GiST 全称为 Generalized Search Tree（通用搜索树）。它本身并非一种具体的索引类型，而是一个基础设施（infrastructure），可在此基础上实现多种索引策略，如 B-tree、R-tree 等。

-- 创建 GiST 索引（几何数据类型）
CREATE INDEX idx_points_gist ON locations USING GIST (geom);

-- 最近邻搜索（KNN）：查找离目标点最近的 10 个点
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

适用场景：

• 地理空间数据（点、线、面）
• 最近邻搜索（KNN，使用 <-> 等距离运算符）
• 范围类型、网络地址类型等

有损索引特性：GiST 索引可能产生假匹配（false matches），需要回表确认实际行数据以消除假匹配。

函数索引（Expression Index）

函数索引（也称表达式索引）的索引键并非表的列，而是表达式或函数的结果。这在业务查询需要对列进行函数变换后再过滤时尤为有用。

-- 创建测试表
CREATE TABLE test (
    id INT,
    info TEXT
);

INSERT INTO test VALUES (1, 'hello'), (2, 'world'), (3, 'Hello');

-- 普通索引无法支持 upper(info) 查询
CREATE INDEX idx_info ON test (info);

-- 不走索引（函数套层）
EXPLAIN SELECT * FROM test WHERE upper(info) = 'HELLO';

-- 创建函数索引
CREATE INDEX idx_info_upper ON test (upper(info));

-- 收集统计信息
ANALYZE test;

-- 现在走索引扫描
EXPLAIN SELECT * FROM test WHERE upper(info) = 'HELLO';

统计信息：函数索引与普通表一样，拥有独立的统计信息，可通过 pg_stats 视图查询。

-- 查看函数索引的统计信息
SELECT tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE tablename = 'idx_info_upper';

部分索引（Partial Index）

部分索引（条件索引）仅针对表中满足特定条件的行创建索引，可显著减小索引体积并降低维护开销。

-- 仅对 id < 500 的行创建索引
CREATE INDEX idx_test_partial ON test (id) WHERE id < 500;

-- 查看索引大小对比
SELECT
    relname,
    pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('idx_test_partial', 'idx_test_id');

适用场景：

• 表中仅小部分数据被频繁查询（如仅查询活跃用户、未删除记录等）
• 索引体积敏感场景
• 写入频繁但仅需索引部分数据的场景

多字段索引（复合索引）

复合索引在单次索引扫描中可同时过滤多个条件，相比多个单列索引的组合效率更高。需注意最左前缀匹配原则：复合索引 (a, b, c) 可有效支持 a、(a, b)、(a, b, c) 的查询条件，但对 b 或 c 单独查询的效率有限。

-- 创建复合索引
CREATE INDEX idx_test_multi ON test (id, info);

-- 可走索引
EXPLAIN SELECT * FROM test WHERE id = 100 AND info = 'hello';

-- 也可走索引（最左前缀）
EXPLAIN SELECT * FROM test WHERE id = 100;

唯一索引（Unique Index）

唯一索引用于强制字段值的唯一性约束。

-- 创建唯一索引
CREATE UNIQUE INDEX idx_test_unique ON test (id);

-- 插入重复值会报错
INSERT INTO test VALUES (1, 'duplicate');  -- 成功
INSERT INTO test VALUES (1, 'duplicate2'); -- 违反唯一约束

死元组与表膨胀风险：PostgreSQL 唯一索引的重复检测机制在底层通过先插入、再检测、最后回滚的方式实现。当插入重复值时，数据库会先写入一条新元组，然后通过索引检测到重复，再将事务标记为无效。这一过程会留下死元组（dead tuple），若频繁插入重复值，将导致表不断膨胀。建议在应用层提前做唯一性校验，减少无效插入。

相关度（Correlation）

correlation 是 pg_stats 视图中的一个字段，表示列值的逻辑顺序与物理存储顺序的关联程度。取值范围为 -1 到 1：

• 接近 1：逻辑顺序与物理顺序高度一致（如自增主键）
• 接近 -1：逻辑顺序与物理顺序高度相反
• 接近 0：逻辑顺序与物理顺序无关

-- 查看列的相关度
SELECT
    tablename,
    attname,
    correlation
FROM pg_stats
WHERE tablename = 'test';

对查询性能的影响：当 correlation 接近 1 时，范围查询（如 WHERE id BETWEEN 1 AND 100）可高效利用索引连续扫描少量数据块；当 correlation 接近 0 时，同一范围查询可能需要扫描大量分散的数据块，导致 I/O 开销显著增加。在索引扫描代价计算中，correlation 是重要输入参数之一。

Index-Only Scan 与 Visibility Map

Index-Only Scan 是 PostgreSQL 9.2 引入的优化特性。其核心条件是查询所需的所有列均包含在索引中（覆盖索引）。但仅有此条件还不够——索引本身不存储元组的可见性信息（事务 ID、删除标记等），因此即使索引包含所有所需列，PostgreSQL 仍需确认元组对当前事务是否可见。

Visibility Map（VM） 解决了这一问题。VM 为每个数据页维护两个标志位，其中 all-visible 位表示该页上所有元组对所有事务均可见。当查询走 Index-Only Scan 时：

1. 从索引读取匹配的条目（含 ctid）
2. 检查对应数据页的 VM 标志位
3. 若 all-visible 为真，直接从索引返回数据（无需回表）
4. 否则，回表确认元组可见性

-- 创建覆盖索引（INCLUDE 子句）
CREATE INDEX idx_test_cover ON test (id) INCLUDE (info);

-- 查询仅需索引列 + INCLUDE 列，可能走 Index-Only Scan
EXPLAIN (ANALYZE, BUFFERS) SELECT id, info FROM test WHERE id BETWEEN 1 AND 100;

VM 文件由 VACUUM 维护。VACUUM 扫描数据页，若某页所有元组均对所有事务可见，则将该页标记为 all-visible。

HOT（Heap-Only Tuple）

HOT（Heap-Only Tuple）是 PostgreSQL 为降低 UPDATE 操作索引维护开销而引入的优化机制。

HOT 更新的触发条件：

1. 不更新任何索引列（包括表达式索引和部分索引的列）
2. 新元组与旧元组位于同一数据页（页内有足够空闲空间）

满足上述条件时，HOT 更新不创建新的索引条目，而是通过版本链（tuple chain）定位最新元组。

-- 查看 HOT 更新统计
SELECT
    schemaname,
    tablename,
    n_tup_hot_upd,
    n_tup_upd,
    round(n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0) * 100, 2) AS hot_update_pct
FROM pg_stat_all_tables
WHERE tablename = 'test';

无法使用 HOT 的场景：

• 更新涉及索引列
• 新元组跨页存储（页内空间不足）

可通过调整表的 fillfactor 参数（默认 100）增加页内空闲空间，从而提高 HOT 更新的命中率。

API 速览

本节梳理博客中涉及的 PostgreSQL 核心 SQL 命令与系统视图。

CREATE INDEX

语法：

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table_name
    [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( column_name [, ...] ) ]
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ WHERE predicate ]

参数说明：

参数	说明
UNIQUE	创建唯一索引，强制唯一性约束
CONCURRENTLY	不阻塞表的 INSERT/UPDATE/DELETE 操作（代价更高、耗时更长）
USING method	索引类型：BTREE（默认）、HASH、BRIN、GIN、GiST、SP-GiST
INCLUDE	覆盖索引，将非搜索列存储在索引中（PostgreSQL 11+）
WITH	存储参数（如 BRIN 的 pages_per_range）
WHERE	部分索引的条件谓词

EXPLAIN

语法：

EXPLAIN [ ( option [, ...] ) ] sql_statement;

常用选项：

选项	说明
ANALYZE	实际执行 SQL 并返回执行统计信息
BUFFERS	显示缓冲区使用情况（需配合 ANALYZE）
VERBOSE	显示详细的计划信息
COSTS	显示启动成本和总成本（默认开启）

系统视图

视图	用途
pg_stats	列级统计信息，含 correlation、n_distinct、most_common_vals 等
pg_class	表和索引的元数据，含 relname、relpages、reltuples
pg_stat_all_tables	表级统计信息，含 n_tup_hot_upd、n_tup_upd 等
pg_indexes	索引定义信息

BRIN 管理函数

函数	说明	适用版本
brin_summarize_new_values(regclass)	摘要所有未摘要的块范围	PostgreSQL 9.5+
brin_summarize_range(regclass, bigint)	摘要包含指定页面的块范围	PostgreSQL 9.5+
brin_desummarize_range(regclass, bigint)	移除指定块范围的摘要	PostgreSQL 11+

完整 Demo 示例

以下示例基于 Node.js + pg 驱动，演示各类索引的创建、查询与性能对比。

运行说明

环境要求：

• Node.js 16+
• PostgreSQL 14+（建议 16 以上版本以支持全部特性）
• 已创建测试数据库

安装依赖：

mkdir pg-index-demo && cd pg-index-demo
npm init -y
npm install pg

创建测试表并插入数据（可直接在 psql 中执行，或通过 Node.js 脚本执行）：

-- 创建测试表
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_no TEXT NOT NULL,
    customer_id INT NOT NULL,
    amount NUMERIC(10,2),
    status TEXT DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT now(),
    tags TEXT[] DEFAULT '{}',
    payload JSONB DEFAULT '{}'
);

-- 插入 100 万条测试数据（示例，实际可调整）
INSERT INTO orders (order_no, customer_id, amount, status, created_at, tags, payload)
SELECT
    'ORD-' || generate_series,
    (random() * 10000)::INT,
    (random() * 1000)::NUMERIC(10,2),
    (ARRAY['pending', 'paid', 'shipped', 'completed', 'cancelled'])[floor(random() * 5) + 1],
    now() - (random() * INTERVAL '365 days'),
    ARRAY['tag' || floor(random() * 10)::TEXT, 'tag' || floor(random() * 10)::TEXT],
    jsonb_build_object('source', (ARRAY['web', 'app', 'api'])[floor(random() * 3) + 1])
FROM generate_series(1, 1000000);

Demo 脚本（index-demo.js）：

const { Client } = require('pg');

const client = new Client({
    host: 'localhost',
    port: 5432,
    database: 'testdb',
    user: 'postgres',
    password: 'postgres'
});

async function runDemo() {
    await client.connect();
    console.log('Connected to PostgreSQL');

    // 1. B-tree 索引（默认）
    console.log('\n=== 1. B-tree Index ===');
    await client.query('DROP INDEX IF EXISTS idx_orders_btree');
    await client.query('CREATE INDEX idx_orders_btree ON orders (customer_id)');
    const btRes = await client.query(
        'EXPLAIN (ANALYZE, BUFFERS, COSTS) SELECT * FROM orders WHERE customer_id = 1234'
    );
    console.log(btRes.rows.map(r => r['QUERY PLAN']).join('\n'));

    // 2. 函数索引
    console.log('\n=== 2. Expression Index ===');
    await client.query('DROP INDEX IF EXISTS idx_orders_upper');
    await client.query('CREATE INDEX idx_orders_upper ON orders (upper(order_no))');
    const funcRes = await client.query(
        'EXPLAIN (ANALYZE, BUFFERS, COSTS) SELECT * FROM orders WHERE upper(order_no) = \'ORD-12345\''
    );
    console.log(funcRes.rows.map(r => r['QUERY PLAN']).join('\n'));

    // 3. 部分索引
    console.log('\n=== 3. Partial Index ===');
    await client.query('DROP INDEX IF EXISTS idx_orders_partial');
    await client.query('CREATE INDEX idx_orders_partial ON orders (status) WHERE status = \'pending\'');
    const partialRes = await client.query(
        'EXPLAIN (ANALYZE, BUFFERS, COSTS) SELECT * FROM orders WHERE status = \'pending\''
    );
    console.log(partialRes.rows.map(r => r['QUERY PLAN']).join('\n'));

    // 4. GIN 索引（数组）
    console.log('\n=== 4. GIN Index (Array) ===');
    await client.query('DROP INDEX IF EXISTS idx_orders_gin');
    await client.query('CREATE INDEX idx_orders_gin ON orders USING GIN (tags)');
    const ginRes = await client.query(
        'EXPLAIN (ANALYZE, BUFFERS, COSTS) SELECT * FROM orders WHERE tags @> ARRAY[\'tag1\']'
    );
    console.log(ginRes.rows.map(r => r['QUERY PLAN']).join('\n'));

    // 5. BRIN 索引（时序）
    console.log('\n=== 5. BRIN Index ===');
    await client.query('DROP INDEX IF EXISTS idx_orders_brin');
    await client.query('CREATE INDEX idx_orders_brin ON orders USING BRIN (created_at) WITH (pages_per_range = 128)');
    const brinRes = await client.query(
        'EXPLAIN (ANALYZE, BUFFERS, COSTS) SELECT * FROM orders WHERE created_at BETWEEN \'2025-01-01\' AND \'2025-01-31\''
    );
    console.log(brinRes.rows.map(r => r['QUERY PLAN']).join('\n'));

    // 6. 覆盖索引（Index-Only Scan）
    console.log('\n=== 6. Covering Index (Index-Only Scan) ===');
    await client.query('DROP INDEX IF EXISTS idx_orders_cover');
    await client.query('CREATE INDEX idx_orders_cover ON orders (customer_id) INCLUDE (amount, status)');
    const coverRes = await client.query(
        'EXPLAIN (ANALYZE, BUFFERS, COSTS) SELECT customer_id, amount, status FROM orders WHERE customer_id BETWEEN 100 AND 200'
    );
    console.log(coverRes.rows.map(r => r['QUERY PLAN']).join('\n'));

    // 7. 查看相关度
    console.log('\n=== 7. Correlation ===');
    const corrRes = await client.query(
        `SELECT attname, correlation FROM pg_stats WHERE tablename = 'orders' AND attname IN ('id', 'customer_id', 'created_at')`
    );
    console.table(corrRes.rows);

    // 8. 查看 HOT 更新统计
    console.log('\n=== 8. HOT Updates ===');
    await client.query('UPDATE orders SET amount = amount + 1 WHERE id < 10000');
    const hotRes = await client.query(
        `SELECT n_tup_upd, n_tup_hot_upd,
         round(n_tup_hot_upd::numeric / NULLIF(n_tup_upd, 0) * 100, 2) AS hot_pct
         FROM pg_stat_all_tables WHERE tablename = 'orders'`
    );
    console.table(hotRes.rows);

    await client.end();
}

runDemo().catch(console.error);

技术点总结

技术点	对应代码
B-tree 索引创建与等值查询	CREATE INDEX ... ON orders (customer_id)
函数索引（表达式索引）	CREATE INDEX ... ON orders (upper(order_no))
部分索引（条件索引）	CREATE INDEX ... ON orders (status) WHERE status = 'pending'
GIN 索引（数组倒排）	CREATE INDEX ... USING GIN (tags)
BRIN 索引（时序数据）	CREATE INDEX ... USING BRIN (created_at) WITH (pages_per_range = 128)
覆盖索引与 Index-Only Scan	CREATE INDEX ... INCLUDE (amount, status)
相关度查询	pg_stats.correlation
HOT 更新统计	pg_stat_all_tables.n_tup_hot_upd

官方文档

• PostgreSQL 官方文档 - 索引类型：https://www.postgresql.org/docs/current/indexes-types.html
• PostgreSQL 官方文档 - B-tree 索引：https://www.postgresql.org/docs/current/btree.html
• PostgreSQL 官方文档 - BRIN 索引：https://www.postgresql.org/docs/current/brin.html
• PostgreSQL 官方文档 - GIN 索引：https://www.postgresql.org/docs/current/gin.html
• PostgreSQL 官方文档 - GiST 索引：https://www.postgresql.org/docs/current/gist.html
• PostgreSQL 官方文档 - 部分索引：https://www.postgresql.org/docs/current/indexes-partial.html
• PostgreSQL 官方文档 - 表达式索引：https://www.postgresql.org/docs/current/indexes-expressional.html
• PostgreSQL 官方文档 - Index-Only Scans：https://www.postgresql.org/docs/current/indexes-index-only-scans.html
• PostgreSQL 官方文档 - Visibility Map：https://www.postgresql.org/docs/current/storage-vm.html
• PostgreSQL 官方文档 - HOT：https://www.postgresql.org/docs/current/storage-hot.html

参考链接

• PostgreSQL Wiki - Indexes：https://wiki.postgresql.org/wiki/Indexes
• PostgreSQL Wiki - BRIN Index：https://wiki.postgresql.org/wiki/BRIN
• PostgreSQL Wiki - GIN Index：https://wiki.postgresql.org/wiki/GIN
• PostgreSQL Wiki - GiST Index：https://wiki.postgresql.org/wiki/GiST
• Use The Index, Luke - PostgreSQL：https://use-the-index-luke.com/sql/example-schema/postgresql

总结

本文系统梳理了 PostgreSQL 的七种核心索引类型（B-tree、Hash、BRIN、GIN、GiST、函数索引、部分索引），以及复合索引、唯一索引的特性和适用场景。

重点阐述了 correlation 相关度对索引扫描代价的影响、Index-Only Scan 与 Visibility Map 的协同机制、以及 HOT 优化如何降低 UPDATE 操作的索引维护开销。

全文基于 PostgreSQL 官方文档，所有代码示例均可在 PostgreSQL 14+ 环境中验证运行。索引选型需结合数据特征、查询模式与写入负载综合评估——没有"万能索引"，只有"最适合的索引"。