隨著搜尋字元串變長，Trigram 搜尋變得更慢

在 Postgres 9.1 數據庫中，我有一個table1大約 150 萬行和一列的表label（為了這個問題，簡化名稱）。

有一個功能三元索引lower(unaccent(label))(unaccent()已被設為不可變以允許其在索引中使用)。

以下查詢非常快：

SELECT count(*) FROM table1
WHERE (lower(unaccent(label)) like lower(unaccent('%someword%')));
count 
-------
    1
(1 row)

Time: 394,295 ms

但以下查詢速度較慢：

SELECT count(*) FROM table1
WHERE (lower(unaccent(label)) like lower(unaccent('%someword and some more%')));
count 
-------
    1
(1 row)

Time: 1405,749 ms

即使搜尋更嚴格，添加更多單詞也會更慢。

我嘗試了一個簡單的技巧來執行第一個單詞的子查詢，然後使用完整的搜尋字元串進行查詢，但是（遺憾的是）查詢規劃器看穿了我的陰謀：

EXPLAIN ANALYZE
SELECT * FROM (
  SELECT id, title, label from table1
  WHERE lower(unaccent(label)) like lower(unaccent('%someword%'))
  ) t1
WHERE lower(unaccent(label)) like lower(unaccent('%someword and some more%'));

table1 上的點陣圖堆掃描（成本=16216.01..16220.04 行=1 寬度=212）（實際時間=1824.017..1824.019 行=1 循環=1）
重新檢查條件： ((lower(unaccent((label)::text)) ~~ '%someword%'::text) AND (lower(unaccent((label)::text)) ~~ '%someword 等等％'：：文本））
-> table1_label_hun_gin_trgm上的點陣圖索引掃描（成本=0.00..16216.01行=1寬度=0）（實際時間=1823.900..1823.900行=1循環=1）
索引條件： ((lower(unaccent((label)::text)) ~~ '%someword%'::text) AND (lower(unaccent((label)::text)) ~~ '%someword 等等％'：：文本））
總執行時間：1824.064 毫秒

我的最終問題是搜尋字元串來自網路界面，該界面可能會發送很長的字元串，因此速度很慢，並且還可能構成 DOS 向量。

所以我的問題是：

• 如何加快查詢速度？
• 有沒有辦法將它分解成子查詢以便更快？
• 也許更高版本的 Postgres 更好？（我嘗試了 9.4，但它似乎沒有更快：仍然是相同的效果。也許更高版本？）
• 也許需要不同的索引策略？

在 PostgreSQL 9.6 中會有一個新版本的 pg_trgm，1.2，在這方面會好很多。稍加努力，你也可以讓這個新版本在 PostgreSQL 9.4 下工作（你必須打更新檔，自己編譯擴展模組並安裝它）。

最舊的版本所做的是搜尋查詢中的每個三元組並取它們的並集，然後應用過濾器。新版本將做的是選擇查詢中最稀有的三元組並只搜尋那個，然後過濾其餘的。

9.1 中不存在執行此操作的機制。在 9.4 中添加了該機器，但當時 pg_trgm 並未適應使用它。

您仍然會遇到潛在的 DOS 問題，因為惡意人員可以製作只有常見三元組的查詢。比如’%and%’，甚至是’%a%'

---

如果您無法升級到 pg_trgm 1.2，那麼另一種欺騙計劃者的方法是：

WHERE (lower(unaccent(label)) like lower(unaccent('%someword%'))) 
AND   (lower(unaccent(label||'')) like 
     lower(unaccent('%someword and some more%')));

通過將空字元串連接到標籤，您可以欺騙計劃者認為它不能在 where 子句的那部分使用索引。所以它只使用 %someword% 上的索引，並對這些行應用過濾器。

---

此外，如果您總是搜尋整個單詞，您可以使用函式將字元串標記為單詞數組，並在該數組返回函式上使用正常內置 GIN 索引（不是 pg_trgm）。

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