postgresql 索引

索引类型：select amname from pg_am;

• btree
• hash
• gist
• gin
• spgist
• brin

Btree索引：默认支持的索引操作符策略

<
<=
=
>=
>

Hash索引：默认支持的索引操作符策略

=

Gin索引：支持多值列的索引。如数组类型,全文检索类型

一维数组类型对应的GIN索引已实现的访问策略操作符
<@ 	/* 被包含array[1,2.3]<@ array[2.3,1] */
@> 	/* 包今 array[1.2.3] @> arravl2] */
= 	/* 相等 array[1,2,3] = array[1,2,3] */
&& 	/* 相交array[1,2,3]&& array[2] */

Gist,并不是单类的索引,可以认为它是一种索引框架支持许多不同的索引策略(operator class)

二维几何类型的以下操作符支持通过Gist索引访问
<<	/* 严格在左侧 例如circle ((o.0).) << circle ((5.0).1) */
&<	/* 表示左边的平面体不会扩展到超过右边的平面体的右边, 例如boxo.0).(1.1))&< box0.0).(2.2)) */
&>	/* 表示左边的平面体不会扩展到超过右边的平面体的左边例如box((0,0),(3,3))&> box(0,0),(2,2)) */
>>	/* 严格在右 */
<<	/* 严格在下 */
&<|	/* 不会扩展到超出上面 */
|&>	/* 不会扩展到超出下面 */
|>>	/* 严格在上 */
@>	/* 包含*/
<@	/* 被包含*/
~=	/* 相同 */
&&	/* 相交 */

Sp-Gist, 与gist类似也是一种索引框架,支持基于磁盘存储的非平衡数据结构,如四叉树,k-d树,radix树

<<
>>
~=
<@
<^
<^	/* 在下面 circle'((0,0), 1)'  < ^circle'((0,5), 1)' */
>^	/* 在下面 circle'((0,5), 1)'  < ^circle'((0,0), 1)' */

使用索引的好处

• 利用索引进行排序减少CPU开销
• 加速带条件的查询,删除,更新
• 加速JOIN操作
• 加速外键约束更新和删除操作
• 加速唯一值约束,排他约束

注意事项

• 正常创建索引时,会阻断除查询以外的其他操作.
• 使用并行CONCURRENTLY 选项后,可以允许同时对表的DML操作,但是对于频繁DML的表,这种创建索引的时间非常长.
• 某些索引不记录WAL,所以如果有利于WAL进行数据恢复的情况(如crash recovery,流复制,warm standby等),这类索引在使用前需要重建.(HASH 索引)

set enable_seqscan=off;