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

postgresql , 索引接口 , 自动创建索引 , 自动选择索引接口 , (btree,hash,bitmap,gin,gist,sp-gist,brin,rum,bloom,zoomdb)

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

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

虽然我在很多文章中有提到过索引的原理以及选择方法，但是一切为懒人服务，所以我们看看如何把创建索引变得更简单，让数据库自动选择最合适的索引方法。

如果你想了解各种索引的用途，你也可以参考一下手册或者以下case的文章，了解索引的原理和应用场景。

<a href="https://github.com/digoal/blog/blob/master/201706/20170612_04.md">《懒人推动社会进步 - 多列聚合, gin与数据分布(选择性)》</a>

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的倒排索引。

在创建索引时，我们需要指定索引方法，抛开其他的原因，对于同一列可以使用多种索引方法建立索引时，到底使用什么方法？

本文接下来的例子将给大家介绍这个问题的解法。

统计时需要使用参与索引的字段的pg_class, pg_stats的统计信息。

将来pg 10还能支持自定义组合列的统计信息收集，比如a,b列的唯一值，相关性等统计信息。支持更加复杂的优化器成本计算。

我讲字段选择索引方法的功能加入到udf中，利用参数控制自动建立索引的名字。要求等。

语法

指定表名，列名，索引名，自动创建合适的索引方法的索引。

例子如下，用户可以更精细的调整内容，自动建立合适的索引。

测试，如下，自动创建bitmap索引。

测试，如下，自动创建brin索引。

用户还可以把其他逻辑写入udf，使得这个自动选择am并创建索引的udf更加智能。