postgresql 索引

索引類型：select amname from pg_am;

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

Btree索引：默認(rèn)支持的索引操作符策略

<
<=
=
>=
>

Hash索引：默認(rèn)支持的索引操作符策略

=

Gin索引：支持多值列的索引。如數(shù)組類型,全文檢索類型

一維數(shù)組類型對應(yīng)的GIN索引已實現(xiàn)的訪問策略操作符
<@ 	/* 被包含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,并不是單類的索引,可以認(rèn)為它是一種索引框架支持許多不同的索引策略(operator class)

二維幾何類型的以下操作符支持通過Gist索引訪問
<<	/* 嚴(yán)格在左側(cè) 例如circle ((o.0).) << circle ((5.0).1) */
&<	/* 表示左邊的平面體不會擴(kuò)展到超過右邊的平面體的右邊, 例如boxo.0).(1.1))&< box0.0).(2.2)) */
&>	/* 表示左邊的平面體不會擴(kuò)展到超過右邊的平面體的左邊例如box((0,0),(3,3))&> box(0,0),(2,2)) */
>>	/* 嚴(yán)格在右 */
<<	/* 嚴(yán)格在下 */
&<|	/* 不會擴(kuò)展到超出上面 */
|&>	/* 不會擴(kuò)展到超出下面 */
|>>	/* 嚴(yán)格在上 */
@>	/* 包含*/
<@	/* 被包含*/
~=	/* 相同 */
&&	/* 相交 */

Sp-Gist, 與gist類似也是一種索引框架,支持基于磁盤存儲的非平衡數(shù)據(jù)結(jié)構(gòu),如四叉樹,k-d樹,radix樹

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

使用索引的好處

• 利用索引進(jìn)行排序減少CPU開銷
• 加速帶條件的查詢,刪除,更新
• 加速JOIN操作
• 加速外鍵約束更新和刪除操作
• 加速唯一值約束,排他約束

注意事項

• 正常創(chuàng)建索引時,會阻斷除查詢以外的其他操作.
• 使用并行CONCURRENTLY 選項后,可以允許同時對表的DML操作,但是對于頻繁DML的表,這種創(chuàng)建索引的時間非常長.
• 某些索引不記錄WAL,所以如果有利于WAL進(jìn)行數(shù)據(jù)恢復(fù)的情況(如crash recovery,流復(fù)制,warm standby等),這類索引在使用前需要重建.(HASH 索引)

set enable_seqscan=off;