自動建立正确索引(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更加智能。