慢速全文搜尋出現率高的術語

我有一個表，其中包含從文本文件中提取的數據。數據儲存在一個名為的列"CONTENT"中，我使用 GIN 創建了該索引：

CREATE INDEX "File_contentIndex"
 ON "File"
 USING gin
 (setweight(to_tsvector('english'::regconfig
          , COALESCE("CONTENT", ''::character varying)::text), 'C'::"char"));

我使用以下查詢對錶執行全文搜尋：

SELECT "ITEMID",
 ts_rank(setweight(to_tsvector('english', coalesce("CONTENT",'')), 'C') , 
 plainto_tsquery('english', 'searchTerm')) AS "RANK"
FROM "File"
WHERE setweight(to_tsvector('english', coalesce("CONTENT",'')), 'C') 
 @@ plainto_tsquery('english', 'searchTerm')
ORDER BY "RANK" DESC
LIMIT 5;

File 表包含 250 000 行，每個"CONTENT"條目由一個隨機單詞和一個對所有行都相同的文本字元串組成。

現在，當我搜尋一個隨機詞（整個表中的 1 個命中）時，查詢執行得非常快（<100 毫秒）。但是，當我搜尋所有行中都存在的單詞時，查詢執行速度非常慢（10 分鐘或更長時間）。

EXPLAIN ANALYZE顯示對於 1-hit 搜尋，執行點陣圖索引掃描，然後執行點陣圖堆掃描。對於慢速搜尋，改為執行Seq Scan，這需要很長時間。

當然，在所有行中都有相同的數據是不現實的。但是由於我無法控制使用者上傳的文本文件，也無法控制他們執行的搜尋，因此可能會出現類似的情況（搜尋數據庫中出現率非常高的術語）。在這種情況下，如何提高搜尋查詢的性能？

執行 PostgreSQL 9.3.4

查詢計劃來自EXPLAIN ANALYZE：

快速搜尋（DB 中的 1 次命中）

"Limit  (cost=2802.89..2802.90 rows=5 width=26) (actual time=0.037..0.037 rows=1 loops=1)"
"  ->  Sort  (cost=2802.89..2806.15 rows=1305 width=26) (actual time=0.037..0.037 rows=1 loops=1)"
"        Sort Key: (ts_rank(setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char"), '''wfecg'''::tsquery))"
"        Sort Method: quicksort  Memory: 25kB"
"        ->  Bitmap Heap Scan on "File"  (cost=38.12..2781.21 rows=1305 width=26) (actual time=0.030..0.031 rows=1 loops=1)"
"              Recheck Cond: (setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char") @@ '''wfecg'''::tsquery)"
"              ->  Bitmap Index Scan on "File_contentIndex"  (cost=0.00..37.79 rows=1305 width=0) (actual time=0.012..0.012 rows=1 loops=1)"
"                    Index Cond: (setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char") @@ '''wfecg'''::tsquery)"
"Total runtime: 0.069 ms"

搜尋速度慢（數據庫中的 25 萬次點擊）

"Limit  (cost=14876.82..14876.84 rows=5 width=26) (actual time=519667.404..519667.405 rows=5 loops=1)"
"  ->  Sort  (cost=14876.82..15529.37 rows=261017 width=26) (actual time=519667.402..519667.402 rows=5 loops=1)"
"        Sort Key: (ts_rank(setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char"), '''cyberspace'''::tsquery))"
"        Sort Method: top-N heapsort  Memory: 25kB"
"        ->  Seq Scan on "File"  (cost=0.00..10541.43 rows=261017 width=26) (actual time=2.097..519465.953 rows=261011 loops=1)"
"              Filter: (setweight(to_tsvector('english'::regconfig, (COALESCE("CONTENT", ''::character varying))::text), 'C'::"char") @@ '''cyberspace'''::tsquery)"
"              Rows Removed by Filter: 6"
"Total runtime: 519667.429 ms"

有問題的案例

…每個 CONTENT 條目由一個隨機單詞和一個對所有行都相同的文本字元串組成。

所有行都相同的文本字元串只是空運。如果需要顯示它，請將其刪除並在視圖中連接它。

顯然，您知道：

誠然，這是不現實的……但由於我無法控製文字……

升級你的 Postgres 版本

執行 PostgreSQL 9.3.4

雖然仍在 Postgres 9.3 上，但您至少應該升級到最新的點版本（目前為 9.3.9）。項目官方推薦：

我們始終建議所有使用者針對正在使用的主要版本執行最新的可用次要版本。

更好的是，升級到9.4，它對GIN 索引進行了重大改進。

主要問題1：成本估算

直到 9.4 版（包括 9.4 版），一些文本搜尋功能的成本被嚴重低估了。在即將發布的 9.5 版中，該成本增加了 100 倍，就像@jjanes 在他最近的回答中描述的那樣：

• PostgreSQL 不使用索引

以下是討論此問題的各個執行緒以及 Tom Lane 的送出消息。

正如您在送出消息中看到的那樣，to_tsvector()是這些功能之一。您可以立即應用更改（作為超級使用者）：

ALTER FUNCTION to_tsvector (regconfig, text) COST 100;

這應該更有可能使用您的功能索引。

主要問題2：KNN

核心問題是 Postgres 必須先計算260k行ts_rank()的排名（這本質上是一個K 最近鄰 (KNN) 問題，並且有針對相關案例的解決方案。但是我想不出適合您情況的通用解決方案，因為排名計算本身取決於使用者輸入。我會嘗試儘早消除大部分低排名匹配，以便只對少數優秀的候選人進行完整的計算。rows=261011

我能想到的一種方法是將全文搜尋與 trigram 相似性搜尋結合起來——這為 KNN 問題提供了一個可行的實現。這樣，您可以預先選擇帶有LIKE謂詞的“最佳”匹配作為候選（LIMIT 50例如在子查詢中），然後根據您在主查詢中的排名計算選擇 5 個排名靠前的行。

或者在同一個查詢中應用兩個謂詞，並根據三元組相似性（這會產生不同的結果）選擇最接近的匹配項，就像在這個相關答案中一樣：

• PostgreSQL FTS 和 Trigram-similarity 查詢優化

我做了更多的研究，你不是第一個遇到這個問題的人。關於 pgsql-general 的相關文章：

• 執行緒 1 (2011)
• 執行緒 2 (2012)
• 執行緒 3 (2014)

tsvector <-> tsquery最終實現運營商的工作正在進行中。

Oleg Bartunov 和 Alexander Korotkov 甚至提出了一個工作原型（使用><操作符代替<->當時的操作），但是集成到 Postgres 中非常複雜，必須重新設計 GIN 索引的整個基礎架構（現在大部分已經完成）。

主要問題3：權重和指數

我還發現了另一個增加查詢速度的因素。根據文件：

GIN 索引對於標準查詢來說沒有損失，但它們的性能對數取決於唯一詞的數量。（**然而，GIN 索引只儲存tsvector值的詞（詞位），而不是它們的權重標籤。**因此，當使用涉及權重的查詢時，需要重新檢查表行。）

大膽強調我的。一旦涉及權重，就必須從堆中獲取每一行（不僅僅是廉價的可見​​性檢查），並且必須取消長值，這會增加成本。但似乎有一個解決方案：

索引定義

再次查看您的索引，開始似乎沒有意義。您為單個列分配權重，只要您不連接具有不同權重的其他列，這是**沒有意義的。

COALESCE()只要您實際上不連接更多列，也沒有任何意義。

簡化您的索引：

CREATE INDEX "File_contentIndex" ON "File" USING gin
(to_tsvector('english', "CONTENT");

你的查詢：

SELECT "ITEMID", ts_rank(to_tsvector('english', "CONTENT")
                      , plainto_tsquery('english', 'searchTerm')) AS rank
FROM   "File"
WHERE  to_tsvector('english', "CONTENT")
      @@ plainto_tsquery('english', 'searchTerm')
ORDER  BY rank DESC
LIMIT  5;

對於匹配每一行的搜尋詞來說仍然很昂貴，但可能要少得多。

旁白

所有這些問題加在一起，你的第二個查詢的 520 秒的瘋狂成本開始變得有意義。但是可能還有更多的問題。你配置你的伺服器了嗎？

所有通常的性能優化建議都適用。

如果您不使用雙引號 CaMeL-case 標識符，它會讓您的生活更輕鬆：

• PostgreSQL 列名是否區分大小寫？

我有類似的問題。我通過針對 field:table 元組預先計算每個流行文本查詢詞的 ts_rank 並將其儲存在查找表中來處理它。這為我在文本繁重的語料庫中搜尋流行詞節省了很多時間（40 倍）。

1. 通過掃描文件併計算其出現次數來獲取語料庫中的流行詞。
2. 按最流行的詞排序。
3. 預先計算流行詞的 ts_rank 並將其儲存在一個表中。

查詢：查找此表並按其各自的排名獲取排序的文件 ID。如果不在那裡，請按照舊方式進行。

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