在数据库SQL查询优化中，索引是最常用的优化手段。合理的使用索引，可以极大提升数据库查询效率，充分利用系统资源。数据库中，数据选择率比较低的时候使用索引可以带来性能提升，但是如果数据选择率比较高，索引额外带来索引读代价，同时会带来随机读的问题，性能不如顺序扫描。因此本文介绍的索引类型只能用于数据选择率比较低的SQL语句。这是选择索引的前提。

Postgresql中主要支持6种类型的索引：BTREE、HASH、GiST、SP-GiSP、GIN、BRIN。可以根据实际的应用场景选择合适的索引，BTREE、HASH是比较常用的索引。

CREATE INDEX默认使用BTREE索引，适合按照顺序存储的数据进行比较查询和范围查询，查询优化器会优先考虑使用BTREE索引，如果涉及到以下任何一种操作：

1）=

2）以及这些操作的组合，比如between and，也可以使用BTREE。

3）在索引列上的IS NULL 或者IS NOT NULL也可以使用BTREE。

4）BTREE索引也可以用于模糊查询，但是仅限字符串开头是常量的情况下，比如 name LIKE ‘Jason%’，或者name ~ ’^Jason’。但是name LIKE ‘%Jason’是不能用的。

5）Min/Max聚集操作也可使用BTREE索引。

6）其实在merge join以及order by中，可以通过使用BTREE索引的有序性来减少sort带来的代价。

例子：

只能处理简单的等值比较，当索引列涉及到等于操作比较时，优化器会考虑使用Hash索引。Hash索引是通过比较hash值来查找定位，如果hash索引列的数据重复度比较高，容易产生严重的hash冲突，从而降低查询效率，因此这种情况下，不适合hash索引。

CREATE INDEX idx_name ON table_name USING HASH (column_name);

例子：

不是独立的索引类型，是一种架构或者索引模板，是一棵平衡二叉树。适用于多维数据类型和集合数据类型，和Btree索引类似，同样适用于其他的数据类型。GiST可以用来做位置搜索，如包含、相交、左边、右边等。和Btree索引相比，GiST多字段索引在查询条件中包含索引字段的任何子集都会使用索引扫描，而Btree索引只有查询条件包含第一个索引字段才会使用索引扫描。GiST索引特定操作符类型高度依赖于索引策略（操作符类）。GiST跟Btree索引相比，索引创建耗时较长，占用空间也比较大。

下面例子中建立了BTREE组合索引(a, b)，如果SQL where条件中有a或者a,b都可以使用该组合索引，但是如果where条件中只有b，则无法使用索引。此时，GiST可以解决这种情况。

例子：

和GiST类似，但是是一棵不平衡树，支持多维和海量数据，把空间分割成互不相交的部分。SP-GiST适用于空间可以递归分割成不相交区域的结构，包括四叉树、k-D树和基数树。

倒排序索引，适合于包含多个组成值的数据，比如数组，全文检索等。用来保存一个键值对的集合，支持用户定义的索引策略，对于不同的索引策略，可以使用不同的操作符。

键值对（Key，postion list）：其中Key是一个键值，而postion list是包含Key的位置值。比如（'Bob'，'10:25 14:3 29:5'） 就表示关键字'Bob'在这些位置（元组TID）上存在。 当我们用关键字'Bob'去查询的时候，一下就定位到包含关键字的元组有这三个。

使用方法如下：

块范围索引，它将数据在磁盘上的block按照一定的数目进行分组，分组之后，计算每组的取值范围。在查找数据时，会遍历这些取值范围，排除掉不在范围之内的分组。BRIN索引适用于存储流式数据日志。例如：按照时间插入的数据，由于数据是按照时间插入，因此数据块上记录的范围信息很少会出现交叉情况，索引过滤后需要比较的数据块也会少很多；反之，如果数据交叉严重，通过索引无法过滤掉任何一个数据块时，操作起来会比全表扫描更加耗时。