聊聊PostgreSQL中的几种索引类型
索引是增强数据库性能的利器，在检索某些特定行的时候效率会有很大提升，postgresql中索引类型丰富，每种索引有着不同的应用场景，下面简单介绍一下。
索引类型与场景
BTree
• =, >, >=, <, <=、排序
Hash
• =
GIN
• 多值类型(数组、全文检索、枚举、网络地址类型)：包含、相交
• JSON类型
• 普通类型(通过btree_gin 插件支持)：与B-Tree类似
• 字符串(通过pg_trgm 插件支持)：模糊查询、相似查询
• 多列：任意列组合查询
GiST
• 空间类型：方位（上、下、左、右），空间关系（相交、包含），空间距离排序(KNN)
• 范围数据：=, &&, <@, @>, <<, >>, -|-, &<, and &>
• 普通类型(通过btree_gist 插件支持)：与B-Tree类似，增加空间类型类似操作符
• 数组类型(通过intarray 插件支持)：与GIN类似
• 多列：任意列组合查询
SPGiST
• 平面几何类型：与GiST类似
• 范围类型：与GiST类似
RUM
• 多值类型(数组、全文检索类型)：包含、相交、相似排序
• 普通类型：与B-Tree类似
BRIN
• 适合线性数据、时序数据，block ranged index是oracle一体机中才有的功能。
• 普通类型：与B-Tree类似
• 空间类型：包含
Bloom
• 多列：任意列组合，等值查询
• 表达式索引
• 搜索条件为表达式
• where st_makepoint(x,y) op ?
• create index idx on tbl ( (st_makepoint(x,y)) );
• 条件索引(定向索引)
• 搜索时，强制过滤某些条件
• where status='active' and col=?
• create index idx on tbl (col) where status='active';
• 监控系统例子select x from tbl where temp>60; -- 99, 1% 异常数据
索引特性
只有B-tree，GiST，GIN和BRIN索引类型支持多列索引。最多可以指定32列。使用最左匹配原则。
在PostgreSQL当前支持的索引类型中，只有B-tree可以产生排序的输出，当ORDER BY与LIMIT n组合：显式排序将必须处理所有数据以识别前n行，但如果存在与ORDER BY匹配的索引，则可以直接检索前n行，而不扫描其余部分。升序默认null值放在最后，可以使用NULLS FIRST和/或NULLS LAST选项来进行调整。
PostgreSQL可以为表达式的结果创建索引，但是该索引维护代价太大，因为每当插入或者更新时，表达式都需要重新计算。
PostgreSQL支持对表中部分数据建立索引，使用部分索引的一个主要原因是避免索引常见值。由于搜索常见值的查询将不会使用索引，所以根本没有必要在索引中保留这些行，这样可以直接排除掉一部分数据，减少了索引的大小，性能更快。
PostgreSQL支持仅索引扫描，当要查询的目标列都在索引中时，直接使用索引中的键值进行返回，不需要回表操作。
技术永无止境，加油吧。
