大家可能還不知道 PostgreSQL 支持對表數據進行局部索引吧? 它的好處是既能加快這部分索引過的數據的讀取速度, 又不會增加額外開銷. 對于那些反復根據給定的 WHERE 子句讀出來的數據, 最好的辦法就是對這部分數據索引. 這對某些需要預先進行聚集計算的特定分析工作流來說, 很合適. 本帖中, 我將舉一個例子說明如何通過部分索引優化數據查詢.

假設有這樣一個事件表, 結構如下:

每個事件關聯一個用戶, 有一個 ID, 一個時間戳, 和一個描述事件的 JSON. JSON 的內容包含頁面的路徑, 事件的類別 (如: 單擊, 網頁瀏覽, 表單提交), 以及其他跟事件相關的屬性。

我們使用這個表存儲各種事件日志. 假設我們手上有個事件自動跟蹤器 , 能自動記錄用戶的每一個點擊, 每一次頁面瀏覽, 每一次表單提交, 以便我們以后做分析. 再假設我們想做個內部用的報表(internal dashboard)顯示一些有價值的數據(high-value metrics), 如:每周的注冊數量, 每天應收帳款. 那么, 問題就來了. 跟這個報表相關的事件, 只占該事件表數據的一小部分 -- 網站的點擊量雖然很高, 但是只有很小一部分最終成交! 而這一小部分成交數據跟其他數據混雜放在一起, 也就是說, 它的信噪比很低.

我們現在想提高報表查詢的速度。先說注冊事件吧，我們把它定義為：注冊頁面(/signup/)的一次表單提交。要獲得九月份第一周的注冊數量，可以理解成：

對一個包含1千萬條記錄, 其中只有 3000 條是注冊記錄, 并且沒有做過索引的數據集, 執行這樣的查詢需要 45 秒.

對單列做全索引(Full Indexes) : 大雜燴

提高查詢速度, 比較傻的辦法是: 給事件相關的各種屬性創建單列索引(single-column index):(data->>'type'),(data->>'path'), 和 time. 通過 bitmap, 我們可以把這三個索引掃描結果合并起來. 如果我們只是有選擇地查詢其中一部分數據, 而且相關索引依然存在內存中, 查詢的速度會變得很快. 剛開始查詢大概用 200 毫秒, 后面會降到 20 毫秒 — 比起要花 45 秒查詢的順序掃描, 確實有明顯的提高.

這種索引方式有幾個弊端:

數據寫入的開銷. 這種方式在每次 INSERT/UPDATE/DELETE 操作的時候, 需要修改這三個索引的數據. 導致像本例這樣頻需要繁寫入數據的更新數據操作代價太高.

數據查詢的限制. 這種方式同時也限制了我們自定義有價值(high-value)事件類型的能力. 比方說, 我們無法在 JSON 字段上做比范圍查詢更復雜的查詢. 具體如:通過正則表達式搜索, 或者查找路徑是/signup/ 開頭的頁面.

磁盤空間的使用. 本例中的提到的表占 6660 mb 磁盤空間, 三個索引和起來有 1026 mb, 隨著時間的推移, 這些數字還會不斷的暴漲.

局部索引（Partial Indexes）

我們分析用的注冊事件，只占了表中全部數據的 0.03%。而全索引是對全部數據進行索引， 顯然不合適。要提高查詢速度， 最好的辦法是用局部索引。

以我們對注冊事件的定義為過濾條件，創建一個無關列(unrelated column)索引，通過該索引，PostgreSQL 很容易找到注冊事件所在的行，查詢速度自然要比在相關字段的3個全索引快的多。 尤其是對時間字段進行局部索引。具體用法如下:

CREATE INDEX event_signups ON event (time)
WHERE (data->>'type') = 'submit' AND (data->>'path') = '/signup/'

這個索引的查詢速度，會從剛開始的 200 毫秒, 降到 2 毫秒。只要多運行查詢語句，速度自然就會加快。更重要的是，局部索引解決了前面提到的全索引的幾個缺點。

索引只占 96 kb 磁盤空間, 是全索引的 1026 mb 的 1/10000。

只有新增的行符合注冊事件的過濾條件， 才更新索引。由于符合條件的事件只有 0.03%，數據寫入的性能得到很大的提高: 基本上，創建和更新這樣的索引沒有太大的開銷。

這樣的局部合并(partial join) 允許我們使用 PostgreSQL 提供的各種表達式作為過濾條件。索引中用到的 WHERE 子句，跟在查詢語句中的用法沒什么兩樣， 所以我們可以寫出很復雜的過濾條件。 如：正則表達式， 函數返回結果，前面提到的前綴匹配。

不要索引結果是布爾值的斷言

我見過有人直接索引布爾表達式：

(data->>'type') = 'submit' AND (data->>'path') = '/signup/'

，然后把時間字段放在第二項. 如：

CREATE INDEX event_signup_time ON event
(((data->>'type') = 'submit' AND (data->>'path') = '/signup/'), time)

這樣做的后果，比上面兩種方法還要嚴重，因為 PostgreSQL 的查詢規劃器（query planner）不會將這個布爾表達式當作過濾條件。也就是說，規劃器不會把它當作 WHERE 語句：

WHERE (data->>'type') = 'submit' AND (data->>'path') = '/signup/'

所以，我們索引的字段：

((data->>'type') = 'submit' AND (data->>'path') = '/signup/')

的值始終為 true。 當我們用這個索引當作條件過濾事件的時候，不管表達式的結果是 true 還是 false，都會先把事件數據讀出來，加載完后，再過濾。

這么一來, 索引的時候會從磁盤中讀取許多不必要的數據, 此外也要檢查每一行數據的有效性. 拿我們例子中的數據集來說, 這樣的查詢第一次要 25 秒, 之后會降到 8 秒. 這樣的結果比索引整個時間字段還要差一些.

局部索引能在很大程度上, 提高那些通過斷言過濾出表中一部分數據的查詢的速度. 對于以流量論英雄(Judging by traffic )的 #postgresql IRC 來說, 局部索引顯得有些資源利用不足. 對比全索引, 局部索引有適用范圍更廣的斷言(greater range of predicates), 配合高選擇性過濾條件(highly selective filters), 寫操作和磁盤空間會變得更少. 要是你經常查詢某個表中的一小部分數據, 應當優先考慮局部索引