1. 索引基础：B-Tree索引原理、索引的物理存储结构、索引与全表扫描的成本对比

1.1 B-Tree索引核心原理

PostgreSQL默认且最通用的索引类型是B-Tree（平衡树）。B-Tree是一种自平衡的多路搜索树，专为磁盘存储优化，能够在对数时间复杂度内完成查找、插入和删除操作。

B-Tree索引的层次结构由以下三层组成：

• 根节点（Root Page）： 树的入口，包含指向子节点的指针和键值范围。
• 内部节点（Internal Page）： 中间层，负责路由查询路径，每个节点包含多个键值和指向下层节点的指针。
• 叶子节点（Leaf Page）： 最底层，存储实际的索引条目（键值 + TID，即行指针），所有叶子节点通过双向链表连接，支持范围扫描。

1.2 索引的物理存储结构

在PostgreSQL中，索引与表数据分开存储。每个索引对应一个独立的物理文件（relfilenode），存储在数据库目录中。理解索引的物理存储结构对于性能调优至关重要。

索引的物理存储布局可以通过以下系统查询进行验证：

-- 查看索引的物理文件路径
SELECT relname, relfilenode, pg_relation_filepath(oid)
FROM pg_class
WHERE relname = 'your_index_name';

-- 查看索引的页分布统计
SELECT * FROM pageinspect.bt_metap('your_index_name');
SELECT * FROM pageinspect.bt_page_stats('your_index_name', 1);

1.3 索引扫描与全表扫描的成本对比

PostgreSQL的查询优化器基于成本模型（Cost Model）在索引扫描和全表扫描之间做出选择。理解成本对比是索引优化的基础。

优化器选择索引扫描的关键阈值取决于选择性（Selectivity）。以下是一个典型的成本对比示例：

-- 示例：100万行表，每页100行，共10000页
-- 查询条件：WHERE status = 'active'（假设选择性为2%）

-- 全表扫描成本估算
-- = 10000页 × 1.0 + 1000000行 × 0.01
-- = 10000 + 10000 = 20000

-- 索引扫描成本估算（假设索引高度3，每页200个索引条目）
-- 索引访问：3页 × 4.0 = 12
-- 回表访问：20000行 × 4.0 / 100行每页 = 800
-- CPU处理：20000行 × 0.01 + 20000索引条目 × 0.005 = 300
-- 总成本 ≈ 12 + 800 + 300 = 1112

-- 结论：索引扫描成本（1112）远低于全表扫描（20000），优化器选择索引扫描

1.4 索引与全表扫描的实战选择策略

在实际生产环境中，索引扫描并非总是优于全表扫描。以下表格总结了选择策略：