这篇文章介绍了PostgreSQL索引的基础知识，包括数据在磁盘中的存储方式、索引如何加速数据访问，以及使用索引带来的存储空间、写入操作、查询规划和内存使用等方面的成本。适合想了解数据库索引原理的读者。

PostgreSQL索引入门指南

本文面向已经对数据库索引有直观了解，但希望深入了解其内部工作原理、权衡取舍以及PostgreSQL提供的各种索引类型的开发者。

索引基础 索引是特殊的数据库对象，主要目的是通过减少磁盘数据读取量来加速数据访问。它们还可用于强制主键、唯一键等约束。索引虽重要，但仅当查询条件与索引列匹配时才有效。通常，当查询返回表中15-20%以下数据时索引才有帮助，否则查询规划器可能选择顺序扫描。

数据存储机制 PostgreSQL将表数据存储在称为"堆"的8KB页面文件中。每行数据（内部称为"元组"）通过ctid（当前元组ID）定位，格式为（页面号,偏移量）。

索引加速原理 通过创建索引（如B树），数据库可直接定位到特定值对应的ctid，避免全表扫描。例如，在百万行表中搜索特定名称，使用索引可将查询时间从265毫秒降至0.077毫秒，读取页面数从6272页减少到4页。

索引的代价 1. 磁盘空间：索引占用额外存储，有时甚至超过表本身大小 2. 写入操作：INSERT/UPDATE/DELETE需要维护索引 3. 查询规划：更多索引会增加查询规划时间 4. 内存使用：索引会占用共享缓冲区内存

索引类型详解 1. B树索引 - 最通用的索引类型，支持范围查询和排序 - 支持多列索引（列顺序很重要） - 支持部分索引（只索引满足条件的行） - 支持覆盖索引（包含查询所需全部字段） - 支持表达式索引（如对函数结果建立索引）

2. 哈希索引

• 仅支持等值查询
• 对长数据（如UUID）更节省空间
• 读取性能在大表上可能优于B树

3. BRIN索引

• 块范围索引，适合大型追加式表
• 对具有物理位置相关性的数据（如时间序列）效果最佳
• 通过pageperrange参数可调整精度

4. GIN索引

• 通用倒排索引，适合复合数据搜索
• 支持数组、JSONB和全文搜索
• 需要配合特定数据类型策略使用

5. GiST/SP-GiST索引

• 提供构建特定索引的框架
• GiST适合地理数据和范围查询
• 在全文搜索场景需要与GIN权衡选择

最佳实践建议 1. 避免为返回大量数据的查询创建索引 2. 考虑使用部分索引减少索引大小 3. 多列索引要注意列顺序 4. 对大表考虑使用BRIN索引 5. 对复合数据使用GIN索引

通过合理选择索引类型和配置，可以在保证查询性能的同时，最小化对存储和写入操作的影响。

总结评论内容：

1. 对书籍内容的肯定

• 认为该书是必读材料，深度适中 "Essential reading. More in-depth than an introduction" (joaomsa) "这看起来对Postgres真的很棒" (turbocon)

2. 推荐其他学习资源

• 推荐了关于B树索引的网站和PostgreSQL官方文档 "this has been my got to site for years" (turbocon) "PostgreSQL文档写得很好且令人愉快" (cdiamand)

3. 对索引使用方法的讨论

• 对多列索引的传统用法提出质疑，讨论新版PostgreSQL的索引扫描方式 "PG was able to use an index, though I believe it was a bitmap index scan" (jihadjihad) "这让我开始质疑长期以来根深蒂固的观点" (jihadjihad)