PostgreSQL之索引(一)索引类型
B-tree
PostgreSQL包括了对标准btree（多路平衡树）索引数据结构的一个实现。任何能够被排序为良定义线性顺序的数据结构都可以用一个btree来索引。唯一的限制是一个索引项不能超过大约三分之一个页面。因为每一种btree操作符类都会在其数据类型上施加一种排序顺序，btree的操作符类（或者实际上是操作符族）已经被用作PostgreSQL对排序语义的一般表达和理解。
B-tree可以在可排序数据上的处理等值和范围查询。PostgreSQL的查询规划器会在任何一种涉及到以下操作符的已索引列上考虑使用B-tree索引（<、<=、=、>=、>）。将这些操作符组合起来，例如BETWEEN和IN，也可以用B-tree索引搜索实现。同样，在索引列上的IS NULL或IS NOT NULL条件也可以在B-tree索引中使用。
优化器也会将B-tree索引用于涉及到模式匹配操作符LIKE和~ 的查询，前提是如果模式是一个常量且被固定在字符串的开头—例如：col LIKE 'foo%'或者col ~ '^foo', 但在col LIKE '%bar'上则不会。
Hash
Hash索引只能处理简单等值比较。不论何时当一个索引列涉及到一个使用了=操作符的比较时，查询规划器将考虑使用一个Hash索引。
CREATE INDEX name ON table USING HASH (column);
GiST
GiST索引并不是一种单独的索引，而是可以用于实现很多不同索引策略的基础设施。相应地，可以使用一个GiST索引的特定操作符根据索引策略（操作符类）而变化。
内建GiST操作符类：
| 名称 | 索引数据类型 | 可索引操作符 | 排序操作符 |
|---|---|---|---|
box_ops |
box |
&& &> &< &<| >> << <<| <@ @> @ |&> |>> ~ ~= |
|
circle_ops |
circle |
&& &> &< &<| >> << <<| <@ @> @ |&> |>> ~ ~= |
<-> |
inet_ops |
inet, cidr |
&& >> >>= > >= <> << <<= < <= = |
|
point_ops |
point |
>> >^ << <@ <@ <@ <^ ~= |
<-> |
poly_ops |
polygon |
&& &> &< &<| >> << <<| <@ @> @ |&> |>> ~ ~= |
<-> |
range_ops |
任何范围类型 | && &> &< >> << <@ -|- = @> @> |
|
tsquery_ops |
tsquery |
<@ @> |
|
tsvector_ops |
tsvector |
@@ |
CREATE INDEX ON my_table USING GIST (my_inet_column inet_ops);
GiST索引也有能力优化“最近邻”搜索，它将找到离给定目标点最近的10个位置。能够支持这种查询的能力同样取决于被使用的特定操作符类。
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
SP-GiST
SP-GiST是空间划分GiST（Space-partitioned GiST）的简称。SP-GiST支持划分搜索树，它们可用于开发许多各种不同的非平衡数据结构，例如四叉树、k-d树和单词查找树。这些结构的共同特征是它们反复地将搜索空间划分成大小不需要相等的分区。匹配这些划分规则的搜索将会很快。
内建 SP-GiST 操作符类：
| 名称 | 索引数据类型 | 可索引操作符 | 排序运算符 |
|---|---|---|---|
kd_point_ops |
point |
<< <@ <^ >> >^ ~= |
<-> |
quad_point_ops |
point |
<< <@ <^ >> >^ ~= |
<-> |
range_ops |
任何范围类型 | && &< &> -|- << <@ = >> @> |
|
box_ops |
box |
<< &< && &> >> ~= @> <@ &<| <<| |>> |&> |
|
poly_ops |
polygon |
<< &< && &> >> ~= @> <@ &<| <<| |>> |&> |
<-> |
text_ops |
text |
< <= = > >= ~<=~ ~<~ ~>=~ ~>~ ^@ |
|
inet_ops |
inet, cidr |
&& >> >>= > >= <> << <<= < <= = |
GIN
GIN 索引是“倒排索引”，它适合于包含多个组成值的数据值，例如数组。倒排索引中为每一个组成值都包含一个单独的项，它可以高效地处理测试指定组成值是否存在的查询。在用于类型point的两种操作符类中，quad_point_ops是默认值。kd_point_ops支持相同的操作符，但是使用一种不同的索引数据结构，在某些应用中它可以提供更好的性能。quad_point_ops、kd_point_ops 和 poly_ops 运算符类别支持<->排序运算符， 支持在索引点或多边形数据集上进行k-最近邻(k-NN)搜索。
内建GIN操作符类：
| 名称 | 索引数据类型 | 可索引操作符 |
|---|---|---|
array_ops |
anyarray |
&& <@ = @> |
jsonb_ops |
jsonb |
? ?& ?| @> @? @@ |
jsonb_path_ops |
jsonb |
@> @? @@ |
tsvector_ops |
tsvector |
@@ @@@ |
在两种用于类型jsonb的操作符类中，jsonb_ops是默认项。
在内部，一个GIN索引包含一个在键上构建的 B 树索引，其中每一个键是一个或者多个被索引项的一个元素（例如，数组的一个成员），并且叶子页中的每一个元组包含一个指向堆指针 B 树的指针（一个“位置树”）或者一个堆指针的简单列表（“位置列表”），只有位置列表小到能够和键值一起放入索引时才使用后一种形式。
BRIN
BRIN表示块范围索引。 BRIN是为处理这样的表而设计的：表的规模非常大，并且其中某些列与它们在表中的物理位置存在某种自然关联。一个块范围是一组在表中物理上相邻的页面，对于每一个块范围在 索引中存储了一些摘要信息。
如果索引中存储的摘要信息与查询条件一致，BRIN 索引可以通过常规的位图索引扫描满足查询，并且将会返回每个范围中所有页面 中的所有元组。查询执行器负责再次检查这些元组并且抛弃掉那些不匹配查询条 件的元组 — 换句话说，这些索引是有损的。由于一个BRIN 索引很小，扫描这种索引虽然比使用顺序扫描多出了一点点开销，但是可能会避 免扫描表中很多已知不包含匹配元组的部分。
