如何將PostgreSQL查詢優化100倍 - Vadim

banq發表於2022-03-09

用來加快PostgreSQL中SELECT-s的一些技巧:帶有冗餘條件的LEFT JOIN、VALUES、擴充套件統計、主鍵型別轉換、CLUSTER、pg_hint_plan

 

LEFT JOIN 的隱藏優勢

這種方法的真正優勢體現在多層JOIN-s上。

PostgreSQL快取了預先過濾的雜湊表,合併它們很便宜,而不可能快取一個巢狀迴圈。

因此,當從INNER JOIN切換到LEFT JOIN時,會取得了10倍到100倍的效能改進。

重要的是:如果你不能保證不同的JOIN-s返回相等的結果,你就必須對空值進行後過濾。

 

Hashing VALUES

如果預測的行數足夠多,PostgreSQL會用HashAggregate over Values Scan產生一個不同的計劃,並可能用Hash Join。這對效能有影響嗎?

有時是的。我看到它在多JOIN查詢中很有用,但只是在計劃者沒有把它安排在所有JOIN-s之後的時候。

在我們的生產中,當 "它起作用 "時,典型的速度提高了10-100倍,但同時,當計劃器變得混亂時,速度也降低了10-100倍。

  

擴充套件統計

事實證明,擴充套件統計對於糾正計劃者的預測特別有用,我們通過客戶賬戶ID進行了軟分片。同樣,我們得到了雜湊連線,而不是巢狀迴圈,速度提高了10-100倍。

 

主鍵型別轉換

當我們的主鍵變成整數而不是字串時,我們很高興這些列上的JOIN-s速度提高了2-5倍。

整數消耗的記憶體更少,而且比較和雜湊也更快。

 

CLUSTER

像許多其他人一樣,我們在將CLUSTER ... USING整合到生產中時遇到了障礙。我們必須定期執行這個命令,因為PostgreSQL不能自動保持叢集的狀態。不幸的是,CLUSTER會獲得一個排他性的表鎖,而且等待的讀寫會被阻塞。我們的救星是pg_repack--一個沒有鎖的輕量級替代品。雲SQL支援它,我們在週末推出了它,取得了巨大的成功。

在CLUSTER之後,我們在生產中的速度提高了2-5倍;特別是,它對那些必須從磁碟讀取緩衝區的冷查詢有幫助。

  

pg_hint_plan

PostgreSQL當局一直反對SQL提示。這種情況類似於Go中對泛型的禁止,只不過Go在13年後終於加入了泛型,而PostgreSQL在36年後還沒有加入提示。幸運的是,暗示可以通過pg_hint_plan插入,這是GitHub上的一個日本專案。雲端SQL從2021年底開始支援pg_hint_plan。

我總是發現,當我確信他們的警告在我的環境中無關緊要時,做一些作者激烈阻止我的事情,是非常令人滿意的。

pg_hint_plan允許許多很酷的技巧。

....

更多點選標題

 

總結

我已經介紹了一些PostgreSQL的查詢效能技巧,這些技巧使我們的查詢速度提高了100倍。

  • 用LEFT JOIN代替INNER JOIN可以幫助計劃器做出更準確的行數預測。新增多餘的ONclauses可以改善Hash Joins。
  • = ANY(VALUES ...)而不是IN可以強制執行一個有很多元素的雜湊聚合。
  • 擴充套件統計Extended statistics可以讓規劃者瞭解列的相關性。
  • 讓表的主鍵成為varchar是個壞主意。
  • 當查詢返回許多相關的記錄時,CLUSTER會起作用。
  • pg_hint_plan提供了強大的提示,包括估計行數修正Rows,JOIN序列執行者Leading,和索引覆蓋IndexScan。儘管後者可能會反擊。
  • 我喜歡explain.tensor.ru來視覺化EXPLAIN-s。
  • 如果你是一個雲SQL使用者,Cloud SQL Insights是必須的。

 

相關文章