PostgreSQL index 淺談及整理

前陣子遇到了 DB 效能的問題才回頭檢視 PostgreSQL index 的基本運作和注意事項。

這篇文章簡單做個記錄，介紹 PG 是如何存放資料、為何加了 index 可以加快查詢速度，以及哪些情況用 index 沒有效果。

PostgreSQL 如何存取資料

資料庫做為存放 persistence data 的單元，如何設計檔案系統將資料存在 HD (SSD) 便是一大課題。

先介紹 PG 的檔案系統的各個名詞：

• heap file(heap): 存放 table 所有資料的檔案
• item(tuple): 表示 table 的單一 row
• block(page): heap file 被分為許多 block，一個 block (8KB) 包含許多 rows

假如系統有一張 customers 的 table, 其內容如下:

在沒有建 index 的情況下，使用者查詢 customers 時， PG 會將整張表載入 memory 裡再根據條件一個個找

可以想像，當這張表很多記錄時，這種操作會多麼的耗時。這也就是我們說的 full table scan

note: 除了上述的情況，還有其它會造成 full table scan 的情境

Index

為了要避免每次查詢時需要 full table scan, 我們可以選定欄位建 index

挑選欄位建 index 時，需要從查詢常用的條件下手。

PostgreSQL 提供多種 index 型態，預設使用 B tree 做為 index 的資料結構

以上個例子舉例，假如塵用程式以 name 查詢 customers，以 name 建立 index 為合理的選擇。其 SQL 如下:

CREATE INDEX ON customers (name);

建立 index 後，使用 name 查詢時，PG 會先去從 index 搜尋資料的位址:

假如 SQL 為:

SELECT * 
FROM customers
WHERE name = 'ALex'

則 PG 能從 index 知道 “Alex” 這筆記錄在 block 0, index 0 的位置。

使用 Index 的限制

建完 index 後，不是每種查詢方式都能利用到它。以 B-Tree 來說，它只支援 < <= = >= > 這幾種條件。

假如用 Like 這種 pattern match 的查詢，在條件是以固定字串為開始的情況，如 name Like 'Alex%' 或是 name Like '^Alex' 能利用到 index。

但假如是 name Like '%ALex' 則無法利用到 index，這點要特別留意。

另外，建立 index 是需要付出代價的。它也是種存放在硬碟的檔案，隨著 table 的資料量增加，index 的用量也會增加。

在資料的 insert/update/delete 都需要更新 index，這些操作也會增加執行時間。

建議定期去整理 table 的 index，將已經沒有使用的 index 刪掉。

Reference

• SQL and PostgreSQL: The Complete Developer’s Guide
• B-Tree Index Type