PostgreSQL全文搜索-選擇低效的執行計劃-有解無憂

假設以下查詢 - 表、列和鍵應該非常明顯（否則請詢問）。

SELECT DISTINCT p.IDProduct
FROM Catalog.Catalog c
INNER JOIN Catalog.Product p ON (
    p.FKIDCatalog=c.IDCatalog
)
INNER JOIN Catalog.ProductLanguage pl ON (
    pl.FKIDProduct=p.IDProduct
    AND (
        pl.FKIDLanguage='de_DE'
        OR pl.FKIDLanguage=c.FKIDLanguage
    )
)
WHERE to_tsvector(SearchConfig, COALESCE(pl.DescriptionShort, '') || ' ' || COALESCE(pl.DescriptionLong, '') || ' ' || COALESCE(pl.KeywordList, '')) @@ to_tsquery('''vorschlaghammer'':*') 
AND c.IDCatalog IN (5, 24, 6, 7, 11, 12, 8, 1, 23)

IN 子句由用戶許可決定，并創建了一個包含 181 次點擊的約 1.3M 產品（共 2M）的搜索空間——這是一個非常典型的用例。不幸的是，回傳結果需要 49 秒。EXPLAIN (analyze, buffers, format text)顯示以下查詢計劃：

 Unique  (cost=59887.83..59887.89 rows=13 width=4) (actual time=48934.329..48972.548 rows=181 loops=1)
   Buffers: shared hit=5386635
   ->  Sort  (cost=59887.83..59887.86 rows=13 width=4) (actual time=48934.328..48972.520 rows=181 loops=1)
         Sort Key: p.idproduct
         Sort Method: quicksort  Memory: 33kB
         Buffers: shared hit=5386635
         ->  Gather  (cost=1045.52..59887.59 rows=13 width=4) (actual time=908.689..48972.460 rows=181 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               Buffers: shared hit=5386635
               ->  Nested Loop  (cost=45.52..58886.29 rows=5 width=4) (actual time=3215.182..48926.270 rows=60 loops=3)
                     Join Filter: (((pl.fkidlanguage)::text = 'de_DE'::text) OR ((pl.fkidlanguage)::text = (c.fkidlanguage)::text))
                     Buffers: shared hit=5386635
                     ->  Hash Join  (cost=45.09..57038.74 rows=1319 width=10) (actual time=0.167..249.085 rows=438115 loops=3)
                           Hash Cond: (p.fkidcatalog = c.idcatalog)
                           Buffers: shared hit=44799
                           ->  Parallel Seq Scan on product p  (cost=0.00..54420.03 rows=979803 width=8) (actual time=0.015..66.259 rows=783365 loops=3)
                                 Buffers: shared hit=44622
                           ->  Hash  (cost=44.98..44.98 rows=9 width=10) (actual time=0.075..0.076 rows=9 loops=3)
                                 Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                 Buffers: shared hit=77
                                 ->  Index Scan using catalog_pkey on catalog c  (cost=0.28..44.98 rows=9 width=10) (actual time=0.033..0.068 rows=9 loops=3)
                                       Index Cond: (idcatalog = ANY ('{5,24,6,7,11,12,8,1,23}'::integer[]))
                                       Buffers: shared hit=77
                     ->  Index Scan using productlanguage_pkey on productlanguage pl  (cost=0.43..1.39 rows=1 width=10) (actual time=0.111..0.111 rows=0 loops=1314345)
                           Index Cond: (fkidproduct = p.idproduct)
                           Filter: (to_tsvector(searchconfig, (((((COALESCE(descriptionshort, ''::character varying))::text || ' '::text) || COALESCE(descriptionlong, ''::text)) || ' '::text) || COALESCE(keywordlist, ''::text))) @@ to_tsquery('''vorschlaghammer'':*'::text))
                           Rows Removed by Filter: 1
                           Buffers: shared hit=5341836
 Planning:
   Buffers: shared hit=65
 Planning Time: 1.905 ms
 Execution Time: 48972.635 ms
(33 rows)

我對執行計劃不是很熟悉，但我會說先獲得 130 萬個產品然后遍歷所有產品以檢查全文條件是不明智的；當然，如果我縮小目錄集的范圍，反之，查詢時間就會減少。但是如果用 eg 替換 IN 子句AND c.IDCatalog<29（它選擇所有主要目錄），查詢優化器會做我期望它首先做的事情（可能是因為它必須考慮“幾乎所有”產品）：

 Unique  (cost=63069.02..63073.42 rows=37 width=4) (actual time=36.778..39.404 rows=265 loops=1)
   Buffers: shared hit=1395
   ->  Gather Merge  (cost=63069.02..63073.33 rows=37 width=4) (actual time=36.777..39.360 rows=265 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=1395
         ->  Sort  (cost=62068.99..62069.03 rows=15 width=4) (actual time=1.269..1.277 rows=88 loops=3)
               Sort Key: p.idproduct
               Sort Method: quicksort  Memory: 37kB
               Buffers: shared hit=1395
               Worker 0:  Sort Method: quicksort  Memory: 25kB
               Worker 1:  Sort Method: quicksort  Memory: 25kB
               ->  Hash Join  (cost=320.56..62068.70 rows=15 width=4) (actual time=0.926..1.229 rows=88 loops=3)
                     Hash Cond: (p.fkidcatalog = c.idcatalog)
                     Join Filter: (((pl.fkidlanguage)::text = 'de_DE'::text) OR ((pl.fkidlanguage)::text = (c.fkidlanguage)::text))
                     Buffers: shared hit=1381
                     ->  Nested Loop  (cost=294.26..62031.43 rows=4171 width=14) (actual time=0.761..1.039 rows=88 loops=3)
                           Buffers: shared hit=1240
                           ->  Parallel Bitmap Heap Scan on productlanguage pl  (cost=293.83..35768.94 rows=4171 width=10) (actual time=0.756..0.819 rows=88 loops=3)
                                 Recheck Cond: (to_tsvector(searchconfig, (((((COALESCE(descriptionshort, ''::character varying))::text || ' '::text) || COALESCE(descriptionlong, ''::text)) || ' '::text) || COALESCE(keywordlist, ''::text))) @@ to_tsquery('''vorschlaghammer'':*'::text))
                                 Heap Blocks: exact=133
                                 Buffers: shared hit=180
                                 ->  Bitmap Index Scan on productlanguage_descriptionshort_descriptionlong_keywordlist  (cost=0.00..291.33 rows=10010 width=0) (actual time=2.208..2.209 rows=265 loops=1)
                                       Index Cond: (to_tsvector(searchconfig, (((((COALESCE(descriptionshort, ''::character varying))::text || ' '::text) || COALESCE(descriptionlong, ''::text)) || ' '::text) || COALESCE(keywordlist, ''::text))) @@ to_tsquery('''vorschlaghammer'':*'::text))
                                       Buffers: shared hit=47
                           ->  Index Scan using product_pkey on product p  (cost=0.43..6.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=265)
                                 Index Cond: (idproduct = pl.fkidproduct)
                                 Buffers: shared hit=1060
                     ->  Hash  (cost=25.99..25.99 rows=25 width=10) (actual time=0.097..0.098 rows=21 loops=3)
                           Buckets: 1024  Batches: 1  Memory Usage: 9kB
                           Buffers: shared hit=41
                           ->  Index Scan using catalog_pkey on catalog c  (cost=0.28..25.99 rows=25 width=10) (actual time=0.036..0.085 rows=21 loops=3)
                                 Index Cond: (idcatalog < 29)
                                 Buffers: shared hit=41
 Planning:
   Buffers: shared hit=68
 Planning Time: 1.903 ms
 Execution Time: 39.517 ms
(38 rows)

這快了 3 個數量級，我希望 PostgreSQL 能夠在另外幾毫秒內過濾 265 個結果行以添加原始 IN 子句。

當然 PostgreSQL 只能猜測走哪條路，但如果它做出如此錯誤的決定，那就很不滿意了。事實上，49 秒的回應時間對于我的用戶來說是完全不能接受的，而 40 毫秒則幾乎不會被注意到。我從未經歷過與非全文查詢類似的事情。

所以可能有兩個問題：a）如何修復/解決這個特定的用例 b）如何在性能方面使用全文查詢？

uj5u.com熱心網友回復：

問題的一個主要根源似乎是您的“產品”到“目錄”的哈希連接被錯誤估計了 300 倍以上。這與 FTS 無關。因此，我想說，您使用 FTS 查詢而不是其他查詢遇到此問題可能只是運氣。

PostgreSQL 會同意首先獲得 130 萬個產品不是一個好主意，但它認為需要獲得大約 4000 個（1319*3）個產品。

那為什么呢？它歸結為p.FKIDCatalog=c.IDCatalog and c.IDCatalog IN (5, 24, 6, 7, 11, 12, 8, 1, 23)。它通過將 FKIDDatalog 的每個值平均匹配的 p 行數乘以 9 來估計這一點。但是您列出的 9 個特定值不是平均值，而是非常常見的值。如果您改為將其寫為p.FKIDCatalog=c.IDCatalog and p.FKIDCatalog IN (5, 24, 6, 7, 11, 12, 8, 1, 23)，那么它將估計它希望為這 9 個特定值中的每一個找到的行并將它們求和。

通常 PostgreSQL 正確估計相等的傳遞屬性，也就是說，如果你把它寫成p.FKIDCatalog=c.IDCatalog and c.IDCatalog=5，它知道它可以得到一個特定的估計p.FKIDCatalog=5并使用它。但它對 IN-list 的傳遞屬性沒有做同樣的事情（除非 IN-list 只有一項長，那么它會重寫為簡單相等并應用傳遞定律），即使在概念上它可以。

我還要注意，在您的其他計劃中可見的全文索引的估計也很糟糕，預計有 4171 行但只找到 88 行。我不知道為什么這會如此糟糕，在我手中電視@@ tq 通常比這更好估計。最近是否對表格進行了分析？至少自從添加了運算式索引以來？

單獨修復其中任何一個可能足以將計劃轉移到更快的計劃。

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/463042.html

標籤：PostgreSQL 全文搜索查询优化

上一篇：使用來自一個查詢的資料分組到另一個查詢中

下一篇：選擇沒有分組依據的列的出現順序