【DB吐槽大會】第58期 - PG 複雜JOIN優化器有巨大提升空間

背景

1、産品的問題點

• PG 複雜JOIN優化器有巨大提升空間

2、問題點背後涉及的技術原理

• PostgreSQL 有兩套JOIN順序、JOIN方法的自動優化方法. (包括子查詢提升後的JOIN).

• • 窮舉.

• • • 有2個問題, 1. 表越多耗時越長(窮舉組合N的階乘-1種), 2. 一次性生成執行計劃, 然後執行, 這種方法随着JOIN層級越深, JOIN相比對記錄的評估會越來越不準确.

• • geqo, 類似圖式算法(TSP)

• • • geqo算出的JOIN順序, 相對來說不是很準确.

3、這個問題将影響哪些行業以及業務場景

• 偏資料分析的業務場景
• ERP系統(例如odoo, sap. 或者企業自己寫的ERP軟體), 使用架構生成的SQL, 通常會有很多表的JOIN(見過幾十個上百個表的JOIN).

4、會導緻什麼問題?

• 無法高效率的得到最優的query plan. 原因是随着JOIN層級越深, JOIN相比對記錄的評估會越來越不準确.

5、業務上應該如何避免這個坑

• 可以使用AQO優化器, 對于複雜query有一定提升.

• • 《PostgreSQL SQL動态優化器 aqo 背景論文》
  • 《[未完待續] PostgreSQL PRO 特性 - AQO(機器學習執行計劃優化器)》
  • 《資料庫優化器原理(含動态規劃、機器學習模組化優化器aqo) - 如何治療選擇綜合症》

• 通過參數和SQL寫法, 固定JOIN順序

• • join_collapse_limit, from_collapse_limit

• 使用HINT
• 使用sr_plan, 篡改執行計劃

6、業務上避免這個坑犧牲了什麼, 會引入什麼新的問題

• 需要非常專業的DBA, 而且随着輸入where條件的變化, 固定的執行計劃并不一定符合所有條件.
• 第三方的aqo插件, 無法保障其品質.

7、資料庫未來産品疊代如何修複這個坑

• 希望核心層面支援多表JOIN的動态優化執行計劃, 而不是一次性生成執行計劃, 然後按計劃執行.

• • 希望可以支援動态優化: （複雜QUERY優化、機器學習、AP類查詢動态根據上一步的實際執行統計資訊(每個node結果集的柱狀圖、高頻詞、記錄數等)調整下一步nodeplan）

https://github.com/digoal/blog/blob/master/202109/20210929_03.md#postgresql-%E8%AE%B8%E6%84%BF%E9%93%BE%E6%8E%A5 https://github.com/digoal/blog/issues/76