PostgreSQL 各种索引类型的区别

PostgreSQL 里各种索引类型（B-Tree、Hash、GiST、GIN、SP-GiST、BRIN）的区别和应用场景：

---

1. B-Tree 索引

• 用途：最常用、默认类型。
• 适合：等值查询（=）、范围查询（<, >, BETWEEN）。
• 适配数据类型：数字、字符串、日期等标准类型。
• 特点：
  • 结构是平衡树（Balanced Tree）。
  • 查询、插入、更新、删除的时间复杂度是 O(log n)。
  • 能很好支持排序（ORDER BY）。

✅ 用例：WHERE id = 5，WHERE price BETWEEN 100 AND 200

---

2. Hash 索引

• 用途：专门用于等值查询。
• 适合：大量 = 查询，不支持范围查询。
• 特点：
  • 内部是哈希表结构。
  • PostgreSQL 10以后才支持 WAL 机制（之前不推荐使用，因为不安全）。
  • 通常来说，等值查询性能比 B-Tree 稍快，但缺点是功能非常有限。

✅ 用例：WHERE email = 'abc@example.com'

---

3. GiST（Generalized Search Tree）

• 用途：适合需要定制"相似性"或"范围性"的数据结构，比如地理空间、全文搜索等。
• 适合：范围查询、近似匹配查询。
• 特点：
  • 通用的框架（不是一个具体的结构），开发者可以定义自己的逻辑。
  • 不保证树是完全平衡的。
  • 用于 PostGIS（地理信息）、全文搜索、模糊搜索等。

✅ 用例：WHERE location && search_area（地理区域重叠查询）

---

4. GIN（Generalized Inverted Index）

• 用途：专门针对包含"多个键值"的数据，比如数组、JSON、全文搜索。
• 适合：包含大量元素的字段，比如数组、JSON字段、全文搜索。
• 特点：
  • 类似倒排索引（每个键对应一堆行）。
  • 查询很快，但构建和更新成本高。
  • 常用于 @>, ?, ?&, ?| 等复杂的包含关系查询。

✅ 用例：WHERE tags @> ARRAY['postgres', 'index']、全文搜索 to_tsvector。

---

5. SP-GiST（Space-Partitioned Generalized Search Tree）

• 用途：适合极度稀疏的数据，比如IP地址、单词前缀匹配。
• 适合：快速找到某一区域、前缀匹配。
• 特点：
  • 与 GiST 类似，但强调"空间划分"，而不是平衡。
  • 更适合在稀疏或者可分割空间数据上使用。

✅ 用例：IP地址段查询、文本前缀查询（WHERE word LIKE 'abc%'）。

---

6. BRIN（Block Range Index）

• 用途：针对超大表（海量数据）设计的低成本索引。
• 适合：大表、数据按照一定顺序插入（比如时间序列）。
• 特点：
  • 不是单行索引，而是对一段块（block range）做摘要（Min/Max值）。
  • 体积极小，非常适合TB级大表。
  • 只能加速大致范围查询，准确率取决于数据有序性。

✅ 用例：按时间查询日志表：WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31'

---

总结一句话

索引类型	场景
B-Tree	常规查询（=, <, >, BETWEEN）
Hash	单纯等值查询（=）
GiST	空间查询、模糊查询
GIN	多值字段（数组、JSON）、全文搜索
SP-GiST	稀疏数据、高效前缀/区域查找
BRIN	海量数据、大表、时间序列