PostgreSQL GIN 索引實戰（一）

GIN 又名 “杜松子酒”，是烈性酒。但是我們今天討論的不是酒，而是 PostgreSQL 中的 GIN 索引，引用 PostgreSQL 源代碼 README 對 GIN 索引的描述。GIN 是廣義反向索引。應被認爲是精靈，不是酒。

Gin stands for Generalized Inverted Index and should be considered as a genie,not a drink.

GIN 索引概述

倒排索引是什麼意思？讓我們通過以下示例來描述。這裏是英文書 “Mastering PostgreSQL 12 Third Edition”，我們把書直接拖到最後幾頁。這會有一個 Index。與書籍前幾章目錄中的 Index 不同，這裏的 Index 是各種各樣的關鍵詞。先把它按 A-Z 來排列，然後 A 下面會有很多 a 開頭的單詞，然後把這些單詞所在的書頁上標註出來。你看，通過這個關鍵字能很快找到書中的相關位置。因此， GIN 索引經常用於全文搜索。

GIN 的索引結構如下：

對應到上面的圖就如下結果：

接下來我們來解釋一下，首先是 entry tree，它是用於組織和存儲 (key, posting list) 鍵值對的 B 樹結構。key 是鍵值， Posting list 或 Posting tree 表示出現過 key 的位置。爲何這裏又是 Postinglist，又是 Posting tree 呢？查看資料發現，如果 tid 的列表很小，則可以和元素放在一個頁面，稱爲 Posting list。如果 tid 的列表很長，則需要有效的數據結構 (B 樹) 來存儲，這樣的樹位於單獨的數據頁上，稱作 Posting Tree。

使用 GIN 索引

首先創建一個擁有 1000 萬用戶的用戶表，其中包括 first_name 和 last_name 兩個列。

CREATE TABLE users (
    first_name text,
    last_name text
)

pgbench=# insert into users SELECT md5(random()::text), md5(random()::text) FROM (SELECT * FROM generate_series(1,10000000) AS id) AS x;
INSERT 0 10000000

執行單列模糊查詢，執行時長 3755ms。

執行兩個列上的模糊查詢，執行時長 3842ms。

立即創建 GIN 索引。需要在創建前安裝 pg_trgm 插件。

pgbench=# CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION
pgbench=# CREATE INDEX idx_user_search ON users USING gin (first_name gin_trgm_ops, last_name gin_trgm_ops);
CREATE INDEX

創建完索引之後，再次執行模糊查詢。

單列模糊查詢，執行時間居然從 3755ms 下降到 12ms。而多列模糊查詢，執行時間從 3842ms 下降到 19ms，性能提升巨大。

後記

今天先研究到這裏，明天繼續 GIN。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/8GYPwO5SQAf12ueJrAfIMw