Postgres 在多次呼叫後將準備好的語句的查詢計劃更改為糟糕的東西

Postgres 13.4

我有一個與此類似的準備好的聲明：

PREPARE my_statement AS 
SELECT * FROM products WHERE (normalized_name = $1 OR distinct_normalized_names @> array['$1']) AND name_matchable = TRUE
ORDER BY name ASC, disambiguation ASC NULLS FIRST, official ASC, release_date DESC, product_code ASC, language ASC LIMIT 1

我在name, normalized_name,上有一個 BTree 索引，name_matchable在distinct_normalized_names.

我在這個查詢的排序部分也有一個 BTree 索引，主要用於與準備好的語句無關的目的name ASC, disambiguation ASC NULLS FIRST, official ASC, release_date DESC, product_code ASC, language ASC

當我準備這個語句並執行EXPLAIN ANALYZE它時，我看到了一個合理的計劃，並且這個準備好的語句執行得非常快：

EXPLAIN ANALYZE EXECUTE my_statement('example');

Limit  (cost=105.26..105.26 rows=1 width=1706) (actual time=0.056..0.057 rows=0 loops=1)
 ->  Sort  (cost=105.26..105.28 rows=48 width=1706) (actual time=0.055..0.056 rows=0 loops=1)
       Sort Key: name, disambiguation NULLS FIRST, official, release_date DESC, product_code, language
       Sort Method: quicksort  Memory: 25kB
       ->  Bitmap Heap Scan on products  (cost=10.16..105.21 rows=48 width=1706) (actual time=0.043..0.044 rows=0 loops=1)
             Recheck Cond: ((normalized_name = 'example'::text) OR (distinct_normalized_names @> '{example}'::text[]))
             Filter: name_matchable
             ->  BitmapOr  (cost=10.16..10.16 rows=48 width=0) (actual time=0.042..0.042 rows=0 loops=1)
                   ->  Bitmap Index Scan on index_products_on_normalized_name  (cost=0.00..2.11 rows=20 width=0) (actual time=0.018..0.018 rows=0 loops=1)
                         Index Cond: (normalized_name = 'example'::text)
                   ->  Bitmap Index Scan on index_products_on_distinct_normalized_names_gin  (cost=0.00..8.04 rows=28 width=0) (actual time=0.023..0.023 rows=0 loops=1)
                         Index Cond: (distinct_normalized_names @> '{example}'::text[])
Planning Time: 1.624 ms
Execution Time: 0.157 ms

但是，如果我呼叫準備好的語句大約十次或更多次，Postgres 會突然將計劃切換到更糟糕的情況，使用我沒有 WHERE 子句的欄位，並且在我DEALLOCATE準備好的語句之前它一直存在。

-- If run this statement 10 or so times, then the plan below is provided
EXPLAIN ANALYZE EXECUTE my_statement('example');

Limit  (cost=53.67..104.57 rows=1 width=1706) (actual time=763.908..763.909 rows=0 loops=1)
 ->  Incremental Sort  (cost=53.67..87248.70 rows=1713 width=1706) (actual time=763.906..763.907 rows=0 loops=1)
       Sort Key: name, disambiguation NULLS FIRST, official, release_date DESC, product_code, language
       Presorted Key: name
       Full-sort Groups: 1  Sort Method: quicksort  Average Memory: 25kB  Peak Memory: 25kB
       ->  Index Scan using index_products_on_name on products  (cost=0.08..87228.90 rows=1713 width=1706) (actual time=763.888..763.888 rows=0 loops=1)
             Filter: (name_matchable AND ((normalized_name = $1) OR (distinct_normalized_names @> ARRAY[$1])))
             Rows Removed by Filter: 338960
Planning Time: 0.015 ms
Execution Time: 764.064 ms

如您所見，總時間從約 3 毫秒激增至 760 毫秒。這個準備好的語句被大量使用，所以這並不理想。我可以通過 ing 語句在負載下的數據庫上實時觀察這個問題PREPARE，執行它大約 10 次，然後看到性能下降和EXPLAIN變化。

看起來 Postgres 突然決定準備好的語句應該使用不同的索引和完全不同的策略，但這是更糟糕的性能。

通過根本不使用準備好的語句，我能夠解決這個問題，如果我使用原始 SQL，即使在重複呼叫之後，我也不會看到問題發生。

為什麼 Postgres 在重複使用後決定更改語句的計劃？

它認為您顯示的通用計劃（其中包含$1）將比自定義計劃（包含實際值）稍快，104.57 與 105.26。所以在執行了許多自定義計劃並認為沒有任何好處之後，它認為每次都制定新的自定義計劃是不值得的。當然，估計還有很長的路要走，但估計是它必須通過的。

你可以set plan_cache_mode = force_custom_plan阻止它這樣做。當然，如果你在全域範圍內設置它，它可能會使其他事情變得更糟，因為現在即使通用計劃非常好，它也會一直重新規劃查詢，而規劃有時可能會成為瓶頸。但是由於您有證據表明使用通用計劃實際上對您來說是一個問題，並且沒有證據表明使用重新計劃會是一個問題，所以我只會進行更改（記錄原因），除非很容易告訴您的客戶不要準備這個特定查詢。

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