建立**索引策略（indexing strategy）**是精通 PostgreSQL 的重要一步：你要能做出有依據的判斷——應用需要哪些索引，以及（更重要的）不需要哪些索引。

索引為系統提供尋找資料的新選項：沒有索引時，資料庫唯一的選擇是循序掃描（sequential scan）整張表；索引存取方法（access method）則直接到資料所在處取用，通常更快。

索引常被當成資料建模（data modeling）活動，這只對了一半：

  • 當索引是用來確保資料一致性（ACID 中的 C）——UNIQUE、PRIMARY KEY、EXCLUDE USING 等約束在 PostgreSQL 中都必須有索引支撐——這時索引策略確實屬於資料建模。
  • 其餘情況，索引的目的都是加速資料存取，而這些存取只發生在執行 SQL 查詢的情境中。

撰寫 SQL 查詢是開發者的工作，所以為應用制定正確的索引策略，也是開發者的工作。

為約束建立的索引#

PostgreSQL 中主鍵、唯一與排除（exclusion）約束必須有索引支撐，原因在於其 **MVCC（Multiversion Concurrency Control，多版本並行控制）**實作：每條 SQL 語句看到的是某個時間點的資料快照，避免看到並行交易未提交的變動，以無鎖的方式換取多使用者環境下的效能。

思考唯一約束怎麼實作就會發現問題：兩個並行交易同時執行

t1> insert into test(id) values(1);
t2> insert into test(id) values(1);

交易開始前表是空的；就各自所見的快照而言，t1 與 t2 都沒有製造重複。但兩者不能都被接受——必須拒絕其中一個。PostgreSQL 的解法是讓內部程式碼以非 MVCC 的方式存取索引資料結構，得以看見尚未提交的進行中交易；這個能力不開放給 SQL 層的使用者。

因此宣告這三種約束時，PostgreSQL 會自動替你建立索引：

> create table test(id integer unique);
CREATE TABLE

> \d test
     Table "public.test"
 Column | Type    | Modifiers
--------+---------+-----------
 id     | integer |
Indexes:
    "test_id_key" UNIQUE CONSTRAINT, btree (id)

系統目錄中也會記錄這個索引是由約束定義而來。

為查詢建立的索引#

PostgreSQL 只自動建立「系統正確運作所必需」的索引；其餘所有索引都由應用程式開發者定義，在需要更快的存取方法時加上。

索引不可能改變查詢結果。它只是提供另一種（多半更快的）存取路徑；查詢的語意與結果集與索引無關。

用 SQL 實作使用者故事是開發者的工作；身為 SQL 語句的作者，開發者也應該負責選擇支撐這些查詢所需的索引。

索引的維護成本#

索引是資料的複本，以特化格式存放、為某類搜尋而優化，而且這份複本同樣遵守 ACID：COMMIT 時，所有寫入主表的變更也必須寫入索引。

每加一個索引，insert、update、delete 等 DML 都多一份交易性的維護成本。除非你有無限的 IO 頻寬與儲存空間，「什麼都索引」是不可行的——所以才需要全局的索引策略。

選擇要優化的查詢#

每個應用都有面向使用者、要求最低延遲的部分，也有多跑一會兒也沒人抱怨的報表查詢。當你發現某條查詢的 explain 計畫缺少索引支援時，先用應用的 SLA 思考：這條查詢值得為了快而讓你多維護幾個索引嗎？

PostgreSQL 的索引存取方法#

存取方法是一個有乾淨 API 的通用演算法，可為相容的資料型別實作；每種演算法各自擅長不同情境，所以 PostgreSQL 同時維護多種。文件所述：B-tree、Hash、GiST、SP-GiST、GIN、BRIN，CREATE INDEX 預設建立適用於最常見情境的 B-tree。

  • B-Tree（平衡樹）：使用率遙遙領先，高效且適用面最廣。PostgreSQL 的 B-tree 實作是同類最佳，並針對並行讀寫優化（理論背景見原始碼 src/backend/access/nbtree/README）。
  • GiST（generalized search tree，廣義搜尋樹）：源自 UC Berkeley 的 GiST 索引研究計畫，處理大量複雜內容的內容式索引。支援二維資料型別（如幾何 point、range 型別）——這些型別沒有全序（total order），無法用 B-tree 正確索引。
  • SP-GiST（space-partitioned GiST）：PostgreSQL 唯一支援非平衡磁碟資料結構的存取方法，如 quadtree、k-d tree、radix tree（trie），適合密度差異很大的二維資料。
  • GIN（generalized inverted index，廣義倒排索引）：處理「被索引的項目是複合值、查詢要找複合值內的元素」的情境——例如文件與文件內的字詞搜尋。倒排索引為每個成分值建立獨立條目，能高效測試特定成分是否存在，是 PostgreSQL 全文檢索（Full Text Search）的基礎。
  • BRIN（block range index，區塊範圍索引）：儲存資料表連續實體區塊範圍的摘要值；對有線性排序的型別，索引內容是每個區塊範圍中該欄位的最小與最大值。
  • Hash：只能處理等值比較（=）。
  • Bloom filter：空間效率極高的「集合成員測試」結構，透過建立索引時決定大小的簽章快速排除不符合的 tuple。適合「表有很多屬性、查詢測試其任意組合」的情境——單一 Bloom 索引能取代原本需要的許多 B-tree 索引，但只支援等值查詢（B-tree 還能做不等式與範圍搜尋）。自 PostgreSQL 9.6 起以擴充套件提供，需先 create extension bloom。

PostgreSQL 10 之前的 Hash 索引不是 crash-safe，絕對不要使用；10 起才可安心使用。

Bloom 與 BRIN 索引主要在涵蓋多欄位時發揮價值；Bloom 尤其適合查詢本身就以等值比較引用其中大部分或全部欄位的情況。

進階索引#

PostgreSQL 文件的索引章節完整涵蓋你需要知道的一切：多欄位索引、索引與 ORDER BY、多索引合併、唯一索引、表達式索引（indexes on expressions）、部分索引（partial indexes）、部分唯一索引、index-only scan 等。本書不重複那些內容，但要做出有依據的索引策略，請完整讀過該章。

加上索引#

決定加哪些索引是索引策略的核心。不是每條查詢都得那麼快，需求多半由使用者定義；而全系統層級的分析可以靠擴充套件 pg_stat_statements——安裝部署後（需註冊到 shared_preload_libraries，要重啟 PostgreSQL），就能列出最常執行的查詢（次數與累計耗時）。

分析步驟建議：

  • 先列出平均執行超過 10 ms（或你的應用合理門檻）的所有查詢。
  • 了解時間花在哪，唯一的方法是 EXPLAIN。看執行期特性時用這個拼法：
explain (analyze, verbose, buffers)
<query here>;
  • 剛開始讀查詢計畫時，善用視覺化工具：https://explain.depesz.com、PostgreSQL Explain Visualizer，或 pgAdmin 內建的。
  • 先檢查估計列數與實際列數的落差。好的統計對查詢規劃器至關重要；若差距達幾個數量級（千倍以上），先確認 Autovacuum Daemon 是否足夠頻繁地 analyze，再考慮調整 statistics target。
  • 最後檢查帶 filter 步驟的循序掃描花了多少時間——那正是索引可能優化的部分。

優化任何系統時記得阿姆達爾定律（Amdahl’s law）：若某步驟佔執行時間 10%，處理它最多也只能省 10%——而通常的做法是整個移除該步驟。

這份粗略指南沒涵蓋「昂貴的函數與表達式可用表達式索引處理」與「排序子句可直接由索引供給」等情況。查詢優化是本書不涵蓋的大題目，正確的索引只是其中一部分；本書涵蓋的是讓你取回應用恰好需要的結果集的所有 SQL 能力。

野外絕大多數慢查詢，仍是回傳太多列給應用程式、拖垮網路與伺服器記憶體——回傳幾百萬列給只在瀏覽器顯示摘要的應用太常見了。SQL 優化的第一守則與程式碼相同：**「我真的需要做這件事嗎？」**最好的查詢優化，是根本不必執行那條查詢。下一章的 SQL 功能，正是讓你以單一查詢取代「查一次、再對每列各查一次」的迴圈。

Backward SQL 就是程式碼 SQL 工具箱 Forward