PostgreSQL（大數據）：按標籤搜尋並按時間戳排序

我們需要在一個大表（200M+ 行）上添加一個搜尋功能：

item_id | tags                          | created_at          | ...
-------------------------------------------------------------------
1       | ['tag1', 'bar2']              | 2020-01-06 12:43:32 |
2       | ['example5', 'tag9', 'foo2']  | 2020-01-10 10:40:00 |
3       | ['test1', 'tag5']             | 2020-01-11 12:43:32 |
...

查詢將與此類似：

SELECT * FROM items 
WHERE tags @> ARRAY['t2', 't5']::varchar[]
ORDER BY created_at DESC
LIMIT 100;

基本上，這就像按標籤搜尋一些日誌並按時間戳對它們進行排序。似乎是一個常見的場景……

我們應該使用什麼索引？你有沒有在生產中測試過類似的東西？

• 範例 1：在標籤上創建 GIN 索引。問題是搜尋可能會返回數百萬個結果，並且為了應用訂單/限制，您需要從磁碟上的表中讀取數百萬次（以便獲取每行的 created_at 值）。
• 範例 2：添加 btree_gin 擴展並在 created_at 和標籤上創建複合索引。問題與上面相同：我認為 PostgreSQL 不能使用排序，因為索引被聲明為 GIN 索引而不是 btree。
• 範例 3：在 created_at 和 tags 上創建一個 btree 索引。PostgreSQL 需要掃描整個索引，因為 btree 不支持數組運算符。我還擔心由於SELECT *PostgreSQL 不會使用僅索引掃描，從而導致從磁碟讀取數百萬次（這實際上是無用的，因為它只需要從磁碟讀取 100 次）。

有兩種方法：

1. 在數組上創建索引：

CREATE INDEX ON items USING gin (tags);

這允許數據庫快速找到匹配的行，但它必須執行 top-n 排序。 2. 在 上創建 B 樹索引created_at：

CREATE INDEX ON items (created_at);

這將允許數據庫避免排序，但它必須掃描並丟棄與條件不匹配的行。

不幸的是，這兩種策略是相互排斥的，哪種策略最好取決於數據。你必須進行實驗。

你有沒有在生產中測試過類似的東西？

作為一般規則，如果可以避免的話，我不會在生產中進行測試。但是，我當然已經在測試中測試過它們。

範例 2：添加 btree_gin 擴展並在 created_at 和標籤上創建複合索引。問題與上面相同：我認為 PostgreSQL 不能使用排序，因為索引被聲明為 GIN 索引而不是 btree

正確，它不會使用它來訂購。

範例 3：在 created_at 和 tags 上創建一個 btree 索引。PostgreSQL 需要掃描整個索引，因為 btree 不支持數組運算符。

它將遍歷索引，直到滿足 LIMIT。如果標籤的選擇性如此之大以至於永遠無法滿足 LIMIT ，它只會讀取整個索引。如果是這種情況，它有望意識到這將是一個問題，並選擇使用範例 1 中的索引。然而，它並不總是很善於意識到這一點。

我還擔心由於 SELECT * PostgreSQL 不會使用僅索引掃描，從而導致從磁碟讀取數百萬次（這實際上是無用的，因為它只需要從磁碟讀取 100 次）。

正確，它不會為此使用僅索引掃描。但是有辦法解決它。假設 item_id 是主鍵，您可以在其上創建索引(created_at, tags, item_id)，然後執行以下操作：

with t as (SELECT item_id FROM items 
   WHERE tags @> ARRAY['t2', 't5']
   ORDER BY created_at DESC
   LIMIT 100)
select * from t join items using (item_id);

另一種選擇是使用RUM 索引。它允許您將 ORDER BY 列附加到數組列上的索引。

CREATE INDEX ON items USING rum (tags rum_anyarray_addon_ops, created_at)
   WITH (attach = 'created_at', to = 'tags');

但是 RUM 索引不支持普通的 ORDER BY，只支持按距離排序。因此，要獲得逆時間順序，您可以在難以置信的遙遠未來選擇某個日期，並按到該日期的距離排序。

SELECT * FROM items 
WHERE tags @> ARRAY['t2', 't5']
ORDER BY created_at <=> '2200-01-01'
LIMIT 100;

引用自：https://dba.stackexchange.com/questions/257535