首先，非常感谢大家对这个系列积极响应，按照要求，这里是对PostgreSQL中索引类型的概述。附带额外材料的辅助视频可以在这里找到。

PostgreSQL有几种流行的索引类型，包括B-tree、Hash、GiST、SP-GiST、GIN和BRIN。实际上，其中有几个是框架，允许你利用别人设计的特殊情况，或者允许定制自己的索引处理器。每种索引类型使用不同的算法，适合于不同类型的查询。

B树

默认情况下，CREATE INDEX命令创建B树（实际上是b+树）索引。是的，就像MySQL一样。B+树对于二分和范围查询很友好。

test=# CREATE TABLE staff (id) as SELECT 'Employee' || x FROM generate_series(1,500) as g(x);
SELECT 500
test=# CREATE INDEX staff_id ON staff(id);
CREATE INDEX
test=# EXPLAIN (verbose) SELECT * FROM staff WHERE id='Employee101';
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Index Only Scan using staff_id on public.staff  (cost=0.27..8.29 rows=1 width=11)
   Output: id
   Index Cond: (staff.id = 'Employee101'::text)
(3 rows)

上面的例子展示了表的创建和数据的填充。然后在唯一的列上创建了索引。然后使用EXPLAIN（带VERBOSE选项）向我们展示了索引是用来加速查询的。

Bitmap

位图非常酷，当一个索引查询可能在同一个堆（数据）页上产生多个命中时，或者在一个查询中使用多个索引时非常有用的。位图索引允许对多个匹配的堆页只访问一次，它们可以通过AND/OR过滤来合并几个索引的结果，而且它们会被优化器自动启用。

Hash

MySQL拥有hash join有段时间了，你可能已经习惯了它们。哈希索引存储从索引列的值派生出32位哈希码。这些值只能通过简单的等值进行比较。当使用相等操作符进行比较涉及到索引列时，查询计划器将考虑使用哈希索引。

GiST

GiST是一个基础设施，在这个基础设施中可以实现许多基于树的不同索引策略。GIST索引不是一种单一的索引。

一些模型包括：

• rtree_gist, btree_gist - R-tree和B-tree的GiST实现。
• intarray - 对一维int4数组的索引支持
• tsearch2 - 一个可搜索（全文检索）的数据类型，具有索引访问功能。
• ltree - 数据类型，索引访问方法，以及查询树状结构的数据。
• hstore - 一个（键，值）数据的存储。
• cube - 数据类型，代表多维立方体。

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

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

这将找到最接近给定目标点的十个地方。

SP-GiST

SP-GiST索引和GiST索引一样，提供了一个支持各种搜索的基础设施。SP-GiST允许实现各种不同的非平衡基于磁盘的数据结构，如quadtree、k-d树和radix trees (tries)。例如，PostgreSQL的标准发行版包括用于二维点的SP-GiST操作符类。

GIN

GIN索引是“倒排索引”，适用于包含多个值的数据，如数组。对于这种类型的索引，可以考虑文本或JSON数据。而GIN类似于MySQL的多值索引，一个键被存储一次，有很多的行指针。

BRIN

BRIN或者Block Range INdexes索引存储了一个表的连续物理块范围内值的摘要。因此，它们对那些数值与表中的行物理顺序密切相关的列最为有效。如果数据是随机的，或者在一个"热"数据库中的关键值有很多变化，那么BRIN的假设可能会被打破。像GiST、SP-GiST和GIN一样，BRIN可以支持许多不同的索引策略，而且根据索引策略的不同，BRIN索引使用不同的运算符。对于具有线性排序顺序的数据类型，索引数据对应于每个块范围的列中数值的最小值和最大值。

支持使用这些运算符的索引查询。“< = >”

在下面的例子中，注意BRIN索引是以KB而不是MB来计算的。

test=# create table demo as SELECT generate_series(1,1000000) as x;
SELECT 1000000
test=# create index btree_idx on demo(x);
CREATE INDEX
test=# create index brin_idx on demo using brin(x);
CREATE INDEX
test=# SELECT relname, pg_size_pretty(pg_relation_size(oid)) 
           FROM pg_class WHERE relname LIKE ’brin_%’ OR relname = ’btree_idx’ 
           ORDER BY relname;
      relname      | pg_size_pretty
-------------------+----------------
 brin_idx          | 24 kB
 brin_random_x     | 24 kB
 btree_idx         | 21 MB

牢记

• B-tree是唯一值查询的理想选择。
• BRIN是多列的索引选择。
• GIN是有许多重复的索引的理想选择。
• SP-GIST是键值有许多重复前缀的索引的理想选择。
• GIST适合其他选择。

推荐阅读

我在准备这篇文章时使用了下面两个链接。PostgreSQL手册是一个很棒的资源，Bruce Momjian是一个宝藏（你应该学习所有他的演讲）。

https://www.postgresql.org/docs/current/indexes-types.html

https://momjian.us/main/writings/pgsql/indexing.pdf

过去为MySQL数据库管理员（DBA）准备的PostgreSQL视频可以在这里找到: episode one, episode two, episode three, episode four, episode five, episode six, episode seven, episode eight, episode nine, 和episode ten.

原文标题：PostgreSQL for MySQL DBAs Episode 11: A Survey of Indexes
原文作者：David Stokes
原文地址：https://www.percona.com/blog/postgresql-for-mysql-dbas-episode-11-a-survey-of-indexes/