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

用來加快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是必須的。