建立正确索引(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法

PostgreSQL的索引接口是开放的，支持btree,hash,bitmap,gin,gist,sp-gist,brin,rum,bloom,zoomdb等索引接口。因此，不同的数据类型，有不同的索引结构可以选择。

由于索引接口众多（应对不同的场景），一些用户可能无法判断应该选择使用哪种索引方法。

创建索引的语法:

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
    ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ]

索引选择的要素

必要约束

1、如果用户要创建 unique 索引，那么只能选择btree索引接口。

2、某些类型指支持某类索引接口，例如数组类型、全文检索类型，只支持GIN索引。GIS类型只支持gist或sp-gist索引。

选择性

如果某个类型支持多种索引接口，那么到底选择哪种接口比较好呢？

和数据的选择性是有关系的。

1、选择性差（例如1亿记录，有100-10万 条唯一值），建议使用gin或bitmap索引。

2、选择性好（例如1亿记录，有8000万唯一值），建议使用btree或hash索引。

数据分布

1、对于数据值与行号呈现较强的线性相关特性时，加入用户没有按该列顺序输出的需求。则建议使用brin块级索引。

2、当列长度超过数据块的1/3时，不能使用btree，建议使用hash索引。或者使用表达式btree索引，建少索引entry的大小。

查询需求

如果某个类型支持多种索引接口，那么到底选择哪种接口比较好呢？

和数据的查询需要也是有关系的。

1、范围查询、排序查询、等值查询

可以使用btree, brin.

2、仅仅有等值查询

可以使用btree, hash

3、有多个列的任意组合查询需求

可以使用bitmap, gin, btree等索引接口

4、有包含、相交等查询需求

可以使用gin等索引接口

5、有距离、距离排序、相交、包含、贯穿等查询需求

可以使用gist等索引接口

性能要求

当一个列支持多种索引接口时，应该选择哪个索引接口，和业务对性能的要求也有关系。

例如，某个列同时支持btree和brin索引，应该选哪个呢？

除了前面提到的线性相关性，还需要考虑业务的查询要求，以及对性能的要求，BTREE对写入性能影响比BRIN大很多，但是明细查询速度，排序速度，limit输出等，都比GIN好很多。

但是当重复值较多时，建议使用GIN，因为它是将元素值作为索引KEY，将行号作为VALUE的倒排索引。