PostgreSQL 中索引分类

PostgreSQL 提供了几种索引类型：

B-tree
Hash
GiST
SP-GiST
GIN
BRIN
扩展 bloom

每种索引类型使用不同的算法，适合不同类型可索引的条件。

默认情况下，CREATE INDEX 命令创建 B-tree 索引，适用于最常见的场景。其他索引类型通过在 CREATE INDEX 命令后面加上 USING 关键字和索引类型名称来选择。例如，创建一个 Hash 索引：

1	CREATE INDEX name ON table USING HASH (column);

一、B-Tree 索引

B-tree 可以处理可以排序成某种顺序的数据上的等值和范围查询。特别是，当索引列涉及到使用以下操作符之一的比较时，PostgreSQL 查询规划器会考虑使用 B-tree 索引：

<
<=
=
>=
>

等价于这些操作符组合的构造，如 BETWEEN 和 IN，也可以通过 B-tree 索引搜索实现。此外，索引列上的 IS NULL 或 IS NOT NULL 条件也可以使用 B-tree 索引。

优化器还可以在涉及模式匹配操作符 LIKE 和 ~ 的查询中使用 B-tree 索引，前提是模式是一个常量并且锚定在字符串的开头——例如，col LIKE 'foo%' 或 col ~ '^foo'，但不是 col LIKE '%bar'。

但是，如果你的数据库不使用 C 语言环境，你需要使用特殊的操作符类来创建索引以支持模式匹配查询；详见 PostgreSQL: Documentation: 16: 11.10. Operator Classes and Operator Families。

B-tree 索引也可以用于 ILIKE 和 ~*，但前提是模式以非字母字符开头，即不受大小写转换影响的字符。

B-tree 索引还可以用于按排序顺序检索数据。这并不总是比简单的扫描和排序快，但通常很有帮助。

1、B-Tree 索引详细介绍

PostgreSQL 包含了标准的 B-tree（多路平衡树）索引数据结构的实现。任何可以排序成良好定义的线性顺序的数据类型都可以由 B-tree 索引索引。唯一的限制是索引条目不能超过大约一页的三分之一（经过 TOAST 压缩后，如果适用的话）。

由于每个 B-tree 操作符类对其数据类型施加了一个排序顺序，B-tree 操作符类（或者更准确地说，操作符家族）已经成为 PostgreSQL 对排序语义的一般表示和理解。因此，它们获得了一些超出仅仅支持 B-tree 索引所需的功能，系统的其他部分，即使与 B-tree 访问方法相距甚远，也会使用这些功能。

2、B-Tree 操作符类的行为

一个 B-tree 操作符类必须提供五个比较操作符：<、<=、=、>= 和 >。可能会期望 <> 也应该包含在操作符类中，但实际上它不在其中，因为几乎不会在索引搜索中使用 <> WHERE 子句。（为了某些目的，规划器将 <> 视为与 B-tree 操作符类相关联；但它通过等号操作符的否定链接找到该操作符，而不是从 pg_amop 中获取。）

当几种数据类型具有近乎相同的排序语义时，它们的操作符类可以归入一个操作符家族。这样做是有利的，因为它允许规划器对跨类型比较做出推断。家族中的每个操作符类应包含其输入数据类型的单类型操作符（及相关支持函数），而跨类型比较操作符和支持函数则是家族中的“松散”成员。建议在家族中包含一组完整的跨类型操作符，从而确保规划器能够表示从传递性推导出的任何比较条件。

一个 B-tree 操作符家族必须满足一些基本假设：

等号操作符 (=) 必须是一个等价关系；即，对于数据类型的所有非空值 A、B、C：
- A = A 是真的（自反律）
- 如果 A = B，则 B = A（对称律）
- 如果 A = B 且 B = C，则 A = C（传递律）
小于操作符 (<) 必须是一个强序关系；即，对于数据类型的所有非空值 A、B、C：
- A < A 是假的（非自反律）
- 如果 A < B 且 B < C，则 A < C（传递律）
- 此外，排序是全序的；即，对于所有非空值 A、B：
  - A < B、A = B 和 B < A 中恰好有一个是真的（三一律）（三一律证明了比较支持函数的定义，当然。）
其他三个操作符（<=、>= 和 >）以显而易见的方式定义为等号操作符 (=) 和小于操作符 (<)，并且必须与其一致。

对于支持多种数据类型的操作符家族，上述定律必须在 A、B、C 取自家族中的任何数据类型时成立。传递律是最难保证的，因为在跨类型情况下，它们表示两个或三个不同操作符的行为是一致的。例如，将 float8 和 numeric 放入同一个操作符家族是不可行的，至少在当前 numeric 值在与 float8 比较时转换为 float8 的语义下不行。由于 float8 的精度有限，这意味着存在不同的 numeric 值会与同一个 float8 值比较相等，从而导致传递律失效。

另一个对多数据类型家族的要求是，家族中包含的数据类型之间的任何隐式或二进制强制转换不得改变相关的排序顺序。

为什么 B-tree 索引要求这些定律在一个单一数据类型内成立应该是相当明显的：没有这些定律，就没有排序来安排键。此外，使用不同数据类型比较键的索引搜索要求比较在两个数据类型之间表现得合理。家族内扩展到三种或更多数据类型不是 B-tree 索引机制本身严格要求的，但规划器依赖这些扩展来进行优化。

二、Hash

Hash 索引存储从索引列的值派生出的 32 位哈希码。因此，这样的索引只能处理简单的等值比较。当索引列涉及到使用等号操作符（=）的比较时，查询规划器会考虑使用 Hash 索引：

三、GiST

GiST 索引不是单一类型的索引，而是一种基础设施，可以在其中实现许多不同的索引策略。因此，GiST 索引可以使用的特定操作符取决于索引策略（操作符类）。例如，PostgreSQL 标准发行版包括几种二维几何数据类型的 GiST 操作符类，支持使用以下操作符的索引查询：

<<
&<
&>
>>
<<|
&<|
|&>
|>>
@>
<@
~=
&&

GiST 索引还能够优化“最近邻”搜索，例如：

1	SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

这条查询语句会找到距离给定目标点最近的十个地点。这种能力再次取决于所使用的特定操作符类。

四、SP-GiST

SP-GiST 索引，像 GiST 索引一样，提供了一种支持各种类型搜索的基础设施。SP-GiST 允许实现多种不同的非平衡磁盘数据结构，如四叉树、k-d 树和基数树（Tries）。例如，PostgreSQL 标准发行版包括用于二维点的 SP-GiST 操作符类，支持使用以下操作符的索引查询：

<<
>>
~=
<@
<<|
|>>

像 GiST 一样，SP-GiST 支持“最近邻”搜索。对于支持距离排序的 SP-GiST 操作符类，相应的操作符列在表 PostgreSQL: Documentation: 16: 69.2. Built-in Operator Classes 的“排序操作符”列中。

五、GIN

GIN 索引是“倒排索引”，适用于包含多个组件值的数据值，如数组。倒排索引为每个组件值包含一个单独的条目，可以高效地处理测试特定组件值存在的查询。

像 GiST 和 SP-GiST 一样，GIN 可以支持许多不同的用户定义的索引策略，GIN 索引可以使用的特定操作符取决于索引策略。例如，PostgreSQL 标准发行版包括一个用于数组的 GIN 操作符类，支持使用以下操作符的索引查询：

<@
@>
=
&&

六、BRIN

BRIN 索引（Block Range Indexes 的缩写）存储关于表中连续物理块范围内存储的值的摘要。因此，它们最适合那些值与表行的物理顺序高度相关的列。

像 GiST、SP-GiST 和 GIN 一样，BRIN 可以支持许多不同的索引策略，BRIN 索引可以使用的特定操作符取决于索引策略。对于具有线性排序顺序的数据类型，索引数据对应于每个块范围内列值的最小值和最大值。这支持使用以下操作符的索引查询：

<
<=
=
>=
>

标准发行版中包含的 BRIN 操作符类记录在表 PostgreSQL: Documentation: 16: 71.2. Built-in Operator Classes。

OB links

OB tags

#PostgreSQL #未完待续