PostgreSQL FTS 和 Trigram-similarity 查詢優化

我最近開始研究PostgreSQL，我有大約12M行要處理，我想在其中應用Full Text Search。我在處理此類數據庫方面沒有任何經驗。我試圖優化查詢，但我懷疑它是否已經完全優化。

現在我正在使用GIST 索引，因為我讀到GIN 索引的更新速度較慢，我的數據庫將定期更新。

我現在只需要關注數據庫的兩列，merchant varchar(80)並且product varchar(400).

我需要使用 FTS 查找產品，並且即使商家拼寫錯誤，我也正在嘗試獲取產品。

我在大約30K行的範例數據庫上執行了一些查詢，以獲得以下結果：

• 首先，我執行基本的 FTS 查詢來分析結果。

explain analyze
select count(*) from products
where to_tsvector('english', product) @@ to_tsquery('hat');

Aggregate  (cost=2027.27..2027.28 rows=1 width=0) (actual time=349.032..349.032 rows=1 loops=1)  
->  Seq Scan on products  (cost=0.00..2026.90 rows=147 width=0) (actual time=43.322..348.961 rows=307 loops=1)
 Filter: (to_tsvector((product)::text) @@ to_tsquery('hat'::text))
Total runtime: 349.140 ms

• 然後我創建了 GIST 索引並執行相同的查詢以查看改進。結果相當不錯。至少對我來說。

create index product_gist on products using gist(to_tsvector('english', product));

Aggregate  (cost=447.17..447.18 rows=1 width=0) (actual time=12.911..12.911 rows=1 loops=1)
->  Bitmap Heap Scan on products  (cost=9.40..446.80 rows=147 width=0) (actual time=2.256..12.776 rows=307 loops=1)
 Recheck Cond: (to_tsvector('english'::regconfig, (product)::text) @@ to_tsquery('hat'::text))
 ->  Bitmap Index Scan on pn  (cost=0.00..9.37 rows=147 width=0) (actual time=2.111..2.111 rows=307 loops=1)
       Index Cond: (to_tsvector('english'::regconfig, (product)::text) @@ to_tsquery('hat'::text))
Total runtime: 13.051 ms

我還測試了 GIN 指數，結果令人驚訝。Total Runtime: 0.583ms 但是我不能使用 GIN 索引，所以讓我們回到 GIST 索引。

• 現在，我正在使用pg_trgm模組來查找兩個單詞之間的相似性（將其用於拼寫錯誤的商家）。

create index merchant_trgm on products using gist(merchant gist_trgm_ops);

select count(*) from products
where to_tsvector('english', product) @@ to_tsquery('hat')
AND   similarity(merchant,'fashion') > 0.2;

Aggregate  (cost=447.64..447.65 rows=1 width=0) (actual time=14.644..14.645 rows=1 loops=1)
->  Bitmap Heap Scan on products  (cost=9.38..447.51 rows=49 width=0) (actual time=2.187..14.635 rows=12 loops=1)
 Recheck Cond: (to_tsvector('english'::regconfig, (product)::text) @@ to_tsquery('hat'::text))
 Filter: (similarity((merchant)::text, 'fashion'::text) > 0.2::double precision)
 ->  Bitmap Index Scan on product_gist  (cost=0.00..9.37 rows=147 width=0) (actual time=2.055..2.055 rows=307 loops=1)
       Index Cond: (to_tsvector('english'::regconfig, (product)::text) @@ to_tsquery('hat'::text))
Total runtime: 14.705 ms

當我在具有12M行的數據庫上執行這些查詢時。顯然這需要更多時間。任何人都可以幫助我進一步減少總執行時間。

我現在腦子裡還有一些問題：

• 我如何搜尋像“WALMART BAGS”這樣的查詢，它將首先返回我與商家 WALMART 的產品 BAG，然後返回來自其他商家的 BAGS。
• 我可以同時使用 GIN 和 GIST 索引嗎？

編輯：

• 我昨晚也執行了這個查詢，得到了以下結果。我已經創建了 GIST 索引，並且檢查了它是否被呼叫。性能仍然沒有達到我的預期。

select count(*) from products 
where (setweight(to_tsvector('english', merchant || ' ' || product), 'A') || 
setweight(to_tsvector('english', product), 'B') ||
setweight(to_tsvector('english', merchant), 'C')) @@ to_tsquery('hat')
AND similarity(merchant,'fashion') > 0.2;

   Aggregate  (cost=450.97..450.98 rows=1 width=0) (actual time=18.228..18.228 rows=1 loops=1)
   ->  Bitmap Heap Scan on products  (cost=9.40..450.84 rows=49 width=0) (actual time=2.399..18.220 rows=12 loops=1)
    Recheck Cond: (((setweight(to_tsvector('english'::regconfig, (((merchant)::text || ' '::text) || (product)::text)), 'A'::"char") || setweight(to_tsvector('english'::regconfig, (product)::text), 'B'::"char")) || setweight(to_tsvector('english'::regconfig, (merchant)::text), 'C'::"char")) @@ to_tsquery('hat'::text))
    Filter: (similarity((merchant)::text, 'fashion'::text) > 0.2::double precision)
    ->  Bitmap Index Scan on products_weighted_index  (cost=0.00..9.39 rows=147 width=0) (actual time=2.206..2.206 rows=307 loops=1)
          Index Cond: (((setweight(to_tsvector('english'::regconfig, (((merchant)::text || ' '::text) || (product)::text)), 'A'::"char") || setweight(to_tsvector('english'::regconfig, (product)::text), 'B'::"char")) || setweight(to_tsvector('english'::regconfig, (merchant)::text), 'C'::"char")) @@ to_tsquery('hat'::text))
   Total runtime: 18.289 ms
   (7 rows)

評估

在您的最後一個查詢中，查找“帽子”的點陣圖索引掃描產生 307 個命中。

Postgres 然後執行點陣圖堆掃描以過濾足夠相似的商家 ( similarity(...) > 0.2)，生成 12 行。您的測試包含 30K 行，因此您的實際查詢將產生大約 300 倍的命中，手頭的測試案例為 90k / 3.5k。一個額外的索引merchant會有所幫助。

建議

我建議您為相似性搜尋創建一個額外的三元組索引。請務必閱讀手冊中有關 trigram 索引支持的章節。我們需要安裝額外的模組pg_trgm（就像你顯然擁有的那樣）。

對於您的第一個請求：

我如何搜尋像“WALMART BAGS”這樣的查詢，它將首先返回我與商家 WALMART 的產品 BAG，然後返回來自其他商家的 BAGS。

我建議使用相似性運算符**%**進行此查詢：

-- SELECT set_limit(0.2)  -- Adjust similarity operator only if needed

SELECT *
FROM   products
WHERE  to_tsvector('english', product) @@ to_tsquery('bag')
AND    merchant % 'walmart'
ORDER  BY merchant <-> 'walmart'
--    LIMIT  n; -- possibly limit to top n results

同樣，您可以在 GiST 和 GIN 之間進行選擇，但這一次 GiST 具有決定性的優勢：

這可以通過 GiST 索引非常有效地實現，但不能通過 GIN 索引。當只需要少量最接近的匹配時，它通常會擊敗第一個公式。

因此，我建議這個索引：

CREATE INDEX prod_merchant_trgm_idx ON products USING gist (merchant gist_trgm_ops);

至於你的第二個要求：

我可以同時使用 GIN 和 GIST 索引嗎？

是的你可以。將兩種類型用於相同的（組合）列幾乎沒有意義，但是 Postgres 可以在同一個查詢中很好地組合 GiST 和 GIN 索引。我再次引用了關於組合多個索引的優秀手冊：

為了組合多個索引，系統掃描每個需要的索引並在記憶體中準備一個點陣圖，給出報告為匹配該索引條件的表行的位置。然後根據查詢的需要對點陣圖進行 AND 和 OR 運算。最後，訪問並返回實際的表行。表格行是按物理順序訪問的，因為這就是點陣圖的佈局方式；ORDER BY這意味著原始索引的任何排序都會失去，因此如果查詢有子句，則需要單獨的排序步驟。出於這個原因，並且由於每次額外的索引掃描都會增加額外的時間，因此規劃器有時會選擇使用簡單的索引掃描，即使還有其他可用的索引也可以使用。

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