使用三元組優化“分類”搜尋表

我一直在閱讀這個出色的答案，以了解 pg_trgm 的工作原理，但我仍然不清楚解決此查詢的最有效方法（在搜尋速度方面有效）：

我有一個表search，我在上面執行三元組搜尋，如下所示：

Column      |  Type   | Modifiers
------------+---------+-----------
id          | bpchar  | collate C
user_id     | integer |
type        | text    |
search_on   | text    | collate C
data        | json    |
Indexes:
"index_search_id" UNIQUE, btree (id)
"index_search_search_on" gist (search_on gist_trgm_ops)
"index_search_type" btree (type)
"index_search_user_id" btree (user_id)

在這種情況下，user_idisNULLable和typeis also NULLable。我執行的查詢相當於這些可能性：

1. 搜尋行(WHERE user_id = 123 OR user_id IS NULL) AND search_on % 'mystring'
2. 搜尋行(WHERE user_id = 123 OR user_id IS NULL) AND type='my-type' AND search_on % 'mystring'

簡單來說，我希望所有具有我的user_id 或 NULL user_id 的行，可選擇按 分類type，並匹配傳入的術語。

現在我只有 3 列上的單獨索引（如上所示），可以根據查詢進行更改。但是，我了解單個索引通常更有效。

是否可以使用進行三元組搜尋的單個索引，但也可以完全匹配它們可以選擇的user_id位置。type``NULL

是否可以使用進行三元組搜尋的單個索引，但也可以完全匹配user_id並且type它們可以選擇為 NULL。

是的，NULL 包含在索引中。您可以像搜尋任何其他值一樣搜尋它。

是的，您可以擁有一個多列三元組 GiST 索引。但是 GiST 索引通常對數據類型沒有意義integer。Btree 索引在各個方面都更好 - 除了您的多列索引的情況。所以 Postgres 預設不安裝需要的操作符類。您需要先安裝附加模組btree_gist，每個數據庫一次：

CREATE EXTENSION IF NOT EXISTS btree_gist;  -- only if not installed, yet

然後你可以創建你的多列索引：

CREATE INDEX foo ON search USING gist (user_id, type, search_on gist_trgm_ops);

相關（附詳細說明）：

• PostgreSQL EXCLUDE USING 錯誤：數據類型整數沒有預設運算符類
• 優化對一系列時間戳（兩列）的查詢

並在您的子句中獲得運算符優先級：WHERE

WHERE (user_id = 123 OR user_id IS NULL)  -- parentheses!
AND    search_on % 'mystring'

或者：

WHERE (user_id = 123 OR user_id IS NULL)
AND   (type = 'my-type' OR type IS NULL)
AND    search_on % 'mystring'

根據數據分佈、基數、謂詞的選擇性、成本設置等。Postgres 可能仍然喜歡在一（或兩）列（偶爾）上的索引。

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