自动建立正确索引(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等索引接口。

标签

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的文章，了解索引的原理和应用场景。

《懒人推动社会进步 - 多列聚合, gin与数据分布(选择性)》

语法

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等索引接口

阅读原文请点击