Postgres 11：升級後查詢計劃使用 seq 掃描
情況
我們有一個託管在 RDS 上的數據庫，其中包含數百個表，其中一些非常大。
我們最近將數據庫從 9.5.22 升級到 11.8，性能顯著下降。
升級後，我們
VACUUM ANALYZE在實例上執行（而不是./analyze_new_cluster.sh因為我們無法在 RDS 實例上執行 shell）。這對局勢沒有幫助。我啟動了另一個獨立的 11.8 數據庫實例並執行了一個
VACUUM FULL ANALYZE，並且該數據庫表現出相同的查詢計劃器行為，因此包含FULL在VACUUM命令中沒有幫助（正如一些 SO 答案中所建議的那樣）。我們發現一個查詢顯示了升級前後性能的最劇烈變化：
SELECT f.uuid, p.name FROM flights f LEFT OUTER JOIN passengers p ON f.uuid = p.flight_id WHERE f.uuid IN (< UUIDs >) ORDER BY f.date_created ASC;以前 P95 延遲低於 4 毫秒。現在，P95 是15 秒。
WHERE當子句中的 UUID 數量包括 5 個或更多 UUID 時，就會出現問題。涉及的表具有以下（簡化的）結構：
Table "public.flights" Column | Type | Modifiers | Storage | Stats target --------------+--------------------------+-----------+---------+-------------- uuid | uuid | not null | plain | date_created | timestamp with time zone | not null | plain | Indexes: "flights_pkey" PRIMARY KEY, btree (uuid) Table "public.passengers" Column | Type | Modifiers | Storage | Stats target -----------+------------------------+-------------------------------+---------+------------- id | bigint | not null default nextval(...) | plain | flight_id | uuid | not null | plain | name | character varying(128) | not null | plain | Indexes: "passengers_pkey" PRIMARY KEY, btree (id) "passengers_a08cee2d" btree (flight_id) Foreign-key constraints: "p_flight_id_75a46b87233dc365_fk_flights_uuid" FOREIGN KEY (flight_id) REFERENCES flights(uuid) DEFERRABLE INITIALLY DEFERRED該
flights表有大約 1700 萬行。該passengers表有大約 26 億行。執行計劃
postgres 9.5 實例（在 WHERE 子句中有 50 個 UUID）
Sort (cost=7273695.73..7273707.45 rows=4688 width=36) (actual time=0.420..0.420 rows=0 loops=1) Sort Key: f.date_created Sort Method: quicksort Memory: 25kB -> Nested Loop Left Join (cost=1652.68..7273409.89 rows=4688 width=36) (actual time=0.408..0.408 rows=0 loops=1) -> Index Scan using flights_pkey on flights f (cost=0.56..428.86 rows=50 width=24) (actual time=0.406..0.406 rows=0 loops=1) Index Cond: (uuid = ANY ('{2c0adac6-79bb-48a1-a0ba-bd8f537d68de,...,a6605812-9a5b-46c4-9989-4d24d195e1c0}'::uuid[])) -> Bitmap Heap Scan on passengers p (cost=1652.12..145082.56 rows=37706 width=28) (never executed) Recheck Cond: (f.uuid = flight_id) -> Bitmap Index Scan on passengers_a08cee2d (cost=0.00..1642.70 rows=37706 width=0) (never executed) Index Cond: (f.uuid = flight_id) Planning time: 0.289 ms Execution time: 0.479 ms (12 rows)postgres 11 實例（在 WHERE 子句中有 50 個 UUID）
Gather Merge (cost=3149109.16..3149552.99 rows=3804 width=36) (actual time=3880.756..3882.219 rows=0 loops=1) Workers Planned: 2 Workers Launched: 2 -> Sort (cost=3148109.14..3148113.89 rows=1902 width=36) (actual time=3878.194..3878.194 rows=0 loops=3) Sort Key: f.date_created Sort Method: quicksort Memory: 25kB Worker 0: Sort Method: quicksort Memory: 25kB Worker 1: Sort Method: quicksort Memory: 25kB -> Nested Loop Left Join (cost=745.27..3148005.54 rows=1902 width=36) (actual time=3878.170..3878.170 rows=0 loops=3) -> Parallel Seq Scan on flights f (cost=0.00..669647.32 rows=21 width=24) (actual time=3878.167..3878.168 rows=0 loops=3) Filter: (uuid = ANY ('{2c0adac6-79bb-48a1-a0ba-bd8f537d68de,...,a6605812-9a5b-46c4-9989-4d24d195e1c0}'::uuid[])) Rows Removed by Filter: 5631600 -> Bitmap Heap Scan on passengers p (cost=745.27..117695.86 rows=32120 width=28) (never executed) Recheck Cond: (f.uuid = flight_id) -> Bitmap Index Scan on passengers_a08cee2d (cost=0.00..737.24 rows=32120 width=0) (never executed) Index Cond: (f.uuid = flight_id) Planning Time: 0.286 ms Execution Time: 3882.262 ms (18 rows)我的最佳評價
passengers在這兩種情況下，都不會執行對錶的掃描。flights這實際上是因為我提供給查詢的 UUID 在表中不存在。我只是想傳遞一個更大的數字來觸發如何掃描flights表的不同行為。在 postgres 9.5 實例中，它使用索引條件執行索引掃描，因為它需要 50 行（我提供給查詢的 UUID 的數量）並且沒有返回（因為它們都不存在）
在 postgres 11 實例中，它希望使用過濾器對錶執行順序掃描（並行）。過濾器實質上刪除了順序掃描返回的所有行。
當傳遞給子句的 UUID 少於 10 個時
WHERE，postgres 11 實例生成與 postgres 9.5 實例上使用的相同的索引掃描查詢計劃。這讓我認為統計數據的差異導致了這種情況，但是對於我檢查的結果，這些統計數據在兩種情況下看起來都相似 - 見下文（除非我沒有提取正確的值，這很可能）。我已經閱讀了許多關於“錯誤查詢”的 SO 答案，但它們沒有解決我認為可能是主要版本升級的結果。
- Postgres 查詢優化（強制索引掃描）
- 防止 PostgreSQL 有時選擇錯誤的查詢計劃
- 索引掃描時 Postgres 不使用索引是更好的選擇
- Postgres 選擇慢得多的 Seq Scan 而不是 Index Scan
我檢查了
default_statistics_target每個數據庫（都是100）和random_page_cost（都是4）。我認識到設置
enable_seqscan為OFF不是永久解決方案，但它確實強制 postgres 11 實例返回與 postgres 9.5 實例相同的查詢計劃。我通過設置進行了實驗
max_parallel_workers_per_gather = 0，這也具有強制 postgres 11 返迴避免順序掃描的查詢計劃的預期效果，但我認為禁用數據庫的該功能並不明智。更改
ORDER BY值（包括將其完全從查詢中刪除）對查詢計劃沒有影響。-- on pg 11 instance with enable_seqscan = OFF OR max_parallel_workers_per_gather = 0 Sort (cost=5901559.44..5901570.85 rows=4566 width=36) Sort Key: f.date_created -> Nested Loop Left Join (cost=745.83..5901281.90 rows=4566 width=36) -> Index Scan using flight_pkey on flight f (cost=0.56..428.99 rows=50 width=24) Index Cond: (uuid = ANY ('{2c0adac6-79bb-48a1-a0ba-bd8f537d68de,...,a6605812-9a5b-46c4-9989-4d24d195e1c0}'::uuid[])) -> Bitmap Heap Scan on passengers p (cost=745.27..117695.86 rows=32120 width=28) Recheck Cond: (f.uuid = flight_id) -> Bitmap Index Scan on passengers_a08cee2d (cost=0.00..737.24 rows=32120 width=0) Index Cond: (f.uuid = flight_id) (9 rows)我已經到了在黑暗中刺傷並試圖比較表中列的
pg_stats值的地步。對於、、和值，它們都顯示相似的值。uuid``flights``null_frac``avg_width``n_distinct``correlation我的問題
鑑於上述情況，我缺少什麼來幫助 postgres 查詢計劃器避免昂貴的順序掃描？
兩個實例之間的所有設置和統計數據似乎都相同，只有 postgres 版本。
9.5 實例沒有任何列的 stats 目標不同於預設值。因此，在有人建議增加該值之前，如果 postgres 9.5 實例在沒有它們的情況下產生“好”計劃，那為什麼會對 postgres 11 實例有所幫助？
postgres 11（並行工作者？）是否有一些東西使它認為它可以比索引掃描更快地執行順序掃描？這似乎不太可能，因為規劃器希望返回 21 行，但成本很高
Parallel Seq Scan on flights f (cost=0.00..669647.32 rows=21謝謝。
編輯：
我們的解決方案
根據回饋，我們通過設置禁用了並行查詢，
max_parallel_workers_per_gather = 0問題就消失了。我們還增加了統計目標（儘管人們表示懷疑這會有所幫助），並將嘗試在未來啟用並行查詢的方法，而不會觸發同樣的“壞”行為。
獎勵：禁用並行查詢之前和之後的查詢延遲圖：
您的統計數據在版本之間沒有太大變化。他們在這兩個方面都有點偏離。但改變的是，由於並行計劃的存在，糟糕的統計數據使並行計劃看起來很有吸引力。
這實際上是因為我提供給查詢的 UUID 在航班表中不存在。我只是想傳遞一個更大的數字來觸發如何掃描航班表的不同行為。
查詢具有高基數的列以查找碰巧不存在的值本質上很難很好地估計。你為什麼做這個？聽起來你這樣做是為了故意製造問題，但聽起來你也遇到了這個問題，因為它是自然發生的。怎麼可能兩者都是真的？也許您人為創建的這個問題與您遇到的自然問題沒有相同的根本原因（或相同的解決方案）。
我通過設置 max_parallel_workers_per_gather = 0 進行了實驗，這也具有強制 postgres 11 返迴避免順序掃描的查詢計劃的預期效果，但我認為禁用數據庫的該功能並不明智。
將其設置為關閉的原因似乎很清楚（假設您的一個範例是實際問題的代表性實例），而您不想將其關閉的原因似乎很模糊。您是否專門進行了升級以訪問並行查詢？
因此，在有人建議增加該值之前，如果 postgres 9.5 實例在沒有它們的情況下產生“好”計劃，那為什麼會對 postgres 11 實例有所幫助？
如果您沒有太多可供選擇的選項，很容易意外地做出正確的決定。開放並行化提供了更多搞砸的方法，而糟糕的統計數據可能會導致選擇其中一種糟糕的方法。話雖如此，增加統計目標無論如何也無濟於事，除非你可以增加到超過 1700 萬，這是你做不到的（即使那樣我也不認為它會有幫助）。
postgres 11（並行工作者？）是否有一些東西使它認為它可以比索引掃描更快地執行順序掃描？這似乎不太可能，因為規劃器希望返回 21 行，但成本很高
它認為從並行查詢中受益的不僅僅是 seq 掃描。通過並行對航班進行 seq 掃描，它認為對乘客的索引掃描也將在本質上並行完成，這就是它認為大部分所謂的好處的來源。雖然這不是一個完整的解釋，但通過關閉 enable_seqscan，我仍然希望使用並行計劃，只是在航班上使用並行索引掃描或併行點陣圖堆掃描。我無法解釋為什麼它會因為 enable_seqscan=off 而完全放棄並行。而且我無法使用模擬數據在 v11 中重現該行為。