PostgreSQL 支持哪些类型的索引？

知识点图片

PostgreSQL 拥有非常强大的索引系统，除了传统的 B-Tree 索引外，它还专门为不同的数据类型和查询场景（如全文检索、空间地理数据、JSON文档、海量时序数据）提供了多种高级索引类型。

PostgreSQL 原生支持以下 6 种基本索引类型：

这是 PostgreSQL 的 默认索引类型。当你执行 CREATE INDEX 而不指定类型时，默认创建的就是 B-Tree 索引。

适用场景：绝大多数常规查询。适合处理可以被排序的数据。
支持的操作符：<、<=、=、>=、>。
特性：
- 支持范围查询（Range Query）和等值查询（Equality Query）。
- 支持 ORDER BY 排序。
- 支持模式匹配查询（如 LIKE 'foo%'，但不支持通配符开头的 LIKE '%foo'）。

基于哈希表实现的索引。

适用场景：仅限于等值查询。
支持的操作符：=。
特性：
- 在 PostgreSQL 10 之前，Hash 索引不记录在 WAL（预写式日志）中，因此崩溃后不安全，且不支持流复制。从 PG 10 开始，Hash 索引已被彻底修复，完全可靠。
- 对于极长的字符串进行等值比较时，Hash 索引的体积通常比 B-Tree 小，且速度可能略快；但在大多数日常场景下，B-Tree 依然是首选。

GiST 不是一种单一的树，而是一个框架，允许开发者实现自定义的索引结构（如 R-Tree）。

适用场景：多维数据、空间/地理数据（如 PostGIS）、范围类型（Range types）、全文检索。
支持的操作符：根据数据类型不同而不同，常见包括 << (在左侧)、&< (重叠)、~ (包含)、@> (包含) 等。
特性：
- 非常擅长处理“相交”、“包含”等几何查询。
- 支持 K-NN（K-Nearest Neighbor，K近邻）搜索，例如“查找距离我当前坐标最近的 5 家餐厅”。

专为内部具有不对称性或重叠的数据结构设计，支持非平衡数据结构（如基数树 Radix Tree、四叉树 Quad-tree）。

类似于搜索引擎底层使用的倒排索引，专门用于处理包含多个元素的值。

适用场景：数组 (Arrays)、JSONB 文档、全文检索 (Full-text search)。
支持的操作符：@> (包含)、<@ (被包含)、&& (重叠)。
特性：
- 它是 PostgreSQL 处理 JSONB 数据的最强武器。如果你要查询 JSON 内部的某个键值对，GIN 是首选。
- 缺点：插入和更新非常慢（因为插入一行数据可能会导致索引中增加成百上千个条目），但查询极快。适合读多写少的场景。

专为海量数据表（如时序数据、物联网数据）设计。

适用场景：数据在物理磁盘上的存储顺序与数据本身的逻辑顺序（如时间戳）高度一致的超大表。
原理：它不为表中的每一行记录索引，而是将表划分为多个“块”（Block），并记录每个块中数据的最大值和最小值。
特性：
- 极度节省空间：索引体积可能只有 B-Tree 的千分之一甚至万分之一。
- 创建速度极快。
- 缺点：如果数据是随机插入、物理分布杂乱无章的，BRIN 索引将毫无用处。

除了上述基本的索引类型，PostgreSQL 还支持以下非常实用的索引构建方式：

多列索引 (Multicolumn Indexes)：
支持在一个索引中包含多个列。最多支持 32 列。
唯一索引 (Unique Indexes)：
保证列中的数据唯一。CREATE UNIQUE INDEX ...（目前只有 B-Tree 原生支持唯一索引）。
表达式索引 (Expression Indexes / Function-Based Indexes)：
不针对列本身，而是针对计算结果建索引。
例如：CREATE INDEX ON users (LOWER(email));，这样查询 WHERE LOWER(email) = 'abc@test.com' 时就能走索引。
部分索引 (Partial Indexes)：
只为表中满足特定条件的行创建索引，极大地节省磁盘空间和提高效率。
例如：CREATE INDEX ON orders (order_id) WHERE status = 'unpaid';（只对未支付的订单建索引）。
覆盖索引 (Covering Indexes - INCLUDE 子句)：
允许在索引的叶子节点中附加非索引列的数据，从而实现仅索引扫描 (Index-Only Scan)，完全不需要回表查数据。
例如：CREATE INDEX ON users (id) INCLUDE (username);

播面