【Postgresql】索引型別（btree、hash、GIST、GIN）

引言

Postgresql 存在許多特定的索引查詢型別，和大部分的Btree為基礎架構的關係型資料庫一樣，在建立索引預設的時候會把btree作為預設值。

本文簡單介紹Postgresql的索引型別，雖然大部分業務常見常見可以用btree搞定，但是某些情況下其他特殊的索引可以有事半功倍的效果。

官方文件

PostgreSQL: Documentation: 14: 11.2. Index Types

pgsql索引型別

• B-tree
• Hash
• GiST
• GIN
• BRIN

B-tree 索引

B-tree 適合處理那些能夠按順序儲存的資料之上的等於（或範圍）查詢。範圍查詢包含下面的內容：

<
<=
=
>=
>

在進行上面這些運算子的運算時候，Postgresql 最佳化器會優先選擇 Btree 索引，除了上面運算子以外還有BETWEEN 和 IN 也可以使用索引， 另外索引列上的IS NULL或IS NOT NULL條件也可以用於B樹索引。

但是實際嘗試發現IS NULL或IS NOT NULL條件的查詢很多時候是走不了索引了。

根據官方的定義，僅當模式是一個常量，並且錨定在字串開頭的時候，最佳化器才會把 B-tree 索引用於模式匹配運算子 LIKE 和 ~。這個說法比較雲裡霧裡，實際上是暗示開發者儘量避開正則或者模糊查詢操作。

模式匹配

比如：col LIKE 'foo%' 或 col ~ '^foo'，這些操作可以認為是可以動用索引的，但是注意col LIKE '%bar'這樣的操作就不可以使用正則，因為幾乎所有資料庫都不支援字尾索引，這和Btree本身的資料結構有關。

這種時候如果要儲存可能需要考慮逆序儲存+索引的方式來觸發掃描索引操作。

B-tree 索引可以用於 ILIKE 和 ~* ，但是和前文說的一樣，僅當模式以非字母字元(不受大小寫影響的字元)開頭才可以使用索引。

B樹索引可以用來檢索排序後的資料，這並不總是比簡單的掃描和排序快，這一點需要注意。

注意：
$：表示匹配字串的結尾。
^：表示匹配輸入字串的開始位置。
 等效於 LIKE， 而 \~\~ 對應 ILIKE，! 和 !\~\~ 運算子 分別代表 NOT LIKE 和 NOT ILIKE

Hash索引

Hash 索引只能處理簡單的等於比較查詢操作，並且注意加入雜湊索引必然是在唯一值的列，否則索引容易失效。

注意官方並不推薦使用雜湊索引。測試表明，PostgreSQL 的 Hash 索引的效能不比 B-tree 索引強，而 Hash 索引的尺寸和製作時間更差。hash索引因為不記錄WAL日誌，所以我們可能需要用 REINDEX 重建 Hash 索引，這會耗費大量系統開銷。

雜湊索引的建立SQL如下：

CREATE INDEX index_name 
ON table_name USING HASH (indexed_column);

GiST 索引(Generalized Search Tree - 通用的搜尋樹)

GiST 的意思是通用的搜尋樹(Generalized Search Tree)。內部是平衡樹的訪問方式，GiST索引通常可以用來替代其他索引，比如Btree。

Gist索引的建立方式如下：

CREATE INDEX gist_idx_test ON GIST_IDX USING gist(circle_dim);

Gist允許自定義型別，自定義資料型別是Gist索引的強項。

但是GiST 索引不是單獨一種索引型別，而更像是一種架構，在這個架構上可以擴充套件出其他的索引，因為Gist索引支援多種索引策略，PostgresSql提供了多個二維幾何資料型別的 GiST 運算子類，這些運算子類支援使用以下運算子的索引查詢：

<<   &<   &>   >>   <<|   &<|   |&>   |>>   @>   <@   ~=   &&

關於這些運算子號的含義可以看下面的官方文件，文件主要介紹了一些三角函式，多邊形，座標運算等等。個人數學渣就不過多展開了。

PostgreSQL: Documentation: 14: 9.11. Geometric Functions and Operators

GiST 索引還能夠最佳化“近鄰”搜尋，例如下面這樣的搜尋：

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

SP-Gist索引

SP-GiST是指空間分割槽的GiST。SP-GiST支援分割槽搜尋樹，便於開發各種不同的非平衡資料結構。

Sp-Gist 索引的建立方式如下：

CREATE INDEX spgist_idx ON spgist_table USING SPGiST (phone_no);

在官方介紹中介紹Sp-Gist的核心如下：

 SP-GiST permits implementation of a wide range of different non-balanced disk-based data structures, such as quadtrees, k-d trees, and radix trees (tries).

SP-GiST 允許實現各種不同的基於磁碟的非平衡資料結構，例如四叉樹、k-d 樹和基數樹（tries）。Postgresql支援二維點位的運算子，這些運算子類支援使用以下運算子的索引查詢：

<<   >>   ~=   <@   <<|   |>>

與 GiST 一樣，SP-GiST 支援“相鄰”搜尋。對於支援距離排序的 SP-GiST 運算子類，在官方文件的 PostgreSQL: Documentation: 14: 66.2. Built-in Operator Classes中列出了相應的運算子。

SP-GiST索引對那些具有自然聚類元素，同時也不是等量平衡樹的資料最有用，例如，GIS、多媒體、電話路由、IP路由等。

GIN索引(Generalized Inverted Index-基因倒排索引)

基因倒排索引(Generalized Inverted Index) 儲存了一個Key/posting list 的結構，Key是唯一鍵。posting list裡面儲存了Key出現的行。

GIN索引的建立方式如下：

CREATE INDEX GIN_idx1 ON student USING GIN (to_tsvector('english', stud_name));

GIN 索引顯然是給搜尋最佳化做準備的，GIN 索引是反轉索引，它可以處理包含多個鍵的值(比如陣列)。Gin和上面提到Gist索引一樣支援自定義索引策略。

PostgreSQL 的標準釋出中包含用於一維陣列的 GIN 運算子類，它支援下面的操作：

<@

@>

=

&&

GIN使用技巧

注意GIN索引的插入是非常慢的，一般建議維護GIN索引是和圖資料庫一樣先刪除後重新建立。另外GIN 有一個可配置的結果集大小軟上限配置引數 gin_fuzzy_search_limit，這個引數可以防止因為海量搜尋匹配進行大量的磁碟讀寫。

預設值 0 表示沒有限制。如果設定了非零值，那麼返回的結果就是從完整結果集中隨機選擇的一部分

侷限性

GIN的侷限性是不能進行完整索引掃描，因為對應多個key，所以堆指標會多次掃描回退，如果索引返回0個Key也會報錯，空白查詢也有可能匹配部分或者完全無法匹配內容。

GIN用於使用等值匹配比較合適，對於複雜的場景查詢結果比較難控制，個人認為適用於輕量級的替代ES這樣的中介軟體進行簡單搜尋的場景可以適用。

BRIN 索引（Block Range Indexes）

BRIN索引（Block Range INdexes的縮寫）儲存了關於儲存在一個表的連續物理塊範圍內的值的摘要，也就是引用資料對應於每個塊範圍的列中數值的最小值和最大值。

BRIN索引的建立方式如下：

CREATE INDEX brin_idx ON test_idx USING BRIN(phone);

像GiST、SP-GiST和GIN一樣，BRIN可以支援許多不同的索引策略，而且根據索引策略的不同，BRIN索引所能使用的特定運算子也不同。

比如Brin索引支援下面的運算子：

<   <=   =   >=   >

在官方文件的 https://www.postgresql.org/docs/current/brin-builtin-opclasses.html#BRIN-BUILTIN-OPCLASSES-TABLE 中有更多運算子的介紹。

小結

從全文列出的索引當中，可以看到用的比較多的基本是Btree、GIN、Brin 這幾個索引，而對於複雜的倒排索引以及Hash索引都有著比較特殊的業務才有可能用到，而Hash索引僅僅適用於等值查詢這樣單一的場景。

參考資料

• (1條訊息) 15.pgsql索引型別（btree、hash、GIST、GIN）\_Lei_Da_Gou的部落格-CSDN部落格_pgsql索引型別
• PostgreSQL: Documentation: 15: 11.2. Index Types