在 PostgreSQL 中強制執行連接順序

作者

Hans-Jürgen Schönig

工程師

譯者簡介

王志斌

PostgreSQL 愛好者

校對者簡介

吳偉略

CET 中電技術研發工程師，PostgreSQL 愛好者

在布拉格舉行的 pgconfeu23 是一個非常出色的活動，在這次活動之後，我決定以博文的形式與大家分享我所介紹的一些情況，或許能對其中的一些話題有所啓發。其中一個想法是 PostgreSQL 處理連接以及連接順序的方式。從內部看，PostgreSQL 在優化查詢方面做得很好，但它到底是如何工作的呢？

讓我們先創建一些表:

plan=# SELECT   'CREATE TABLE x' || id || ' (id int)'
 FROM      generate_series(1, 5) AS id;
         ?column?         
--------------------------
 CREATE TABLE x1 (id int)
 CREATE TABLE x2 (id int)
 CREATE TABLE x3 (id int)
 CREATE TABLE x4 (id int)
 CREATE TABLE x5 (id int)
(5 rows)

在 PostgreSQL 中，我們可以很容易地創建 SQL。psql 的美妙之處在於，我們可以簡單地運行 \ gexec 將之前的輸出作爲新的輸入使用。

plan=# \gexec
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

我們有 5 個表可以作爲示例數據結構。

在 PostgreSQL 中連接表

以下查詢展示了使用我們剛剛創建的表進行簡單連接的示例：

plan=# explain (timing, analyze)  SELECT *
           FROM    x1 JOIN x2 ON  (x1.id = x2.id)
                      JOIN x3 ON  (x2.id = x3.id)
                      JOIN x4 ON  (x3.id = x4.id)
                      JOIN x5 ON  (x4.id = x5.id);
…
Planning Time: 0.297 ms
Execution Time: 0.046 ms

在這裏需要重點觀察的是計劃時間。PostgreSQL 需要 0.297 毫秒來找到運行查詢的最佳執行計劃（執行策略）。引發的問題是：規劃器在何處需要時間來計劃查詢？事實是：即使如上面展示的使用顯式連接，PostgreSQL 也會隱式連接這些表，並決定最佳連接順序。這在現實生活中意味着什麼？我們考慮一個連接 “a join b join c”：即使我們編寫了一個 SQL 命令說將 “a 連接到 b”，優化器可能仍然決定投票支持 “c join a join b”，因爲它能保證得到相同的結果。爲什麼會這樣？因爲它可能大大提高查詢的效率。讓優化器決定最佳連接順序是一種重要的內部優化。

然而，我們必須關注執行計劃時間 - 尤其是如果涉及許多表（10 個以上的表）。

在 SQL 中控制連接行爲

如果執行計劃時間是一個問題，我們可以強制 PostgreSQL 使用我們期望的連接順序。控制這種行爲的變量是 join_collapse_limit。這意味着什麼？基本上它控制了計劃中的隱式顯式連接的數量。換句話說：PostgreSQL 可以優化多少個顯式連接。

如果我們將此變量設置爲 1，這意味着我們強制 PostgreSQL 使用我們選擇的連接順序：

plan=# SET join_collapse_limit TO 1;
SET
plan=# explain (timing, analyze)  SELECT *
           FROM    x1 JOIN x2 ON  (x1.id = x2.id)
                      JOIN x3 ON  (x2.id = x3.id)
                      JOIN x4 ON  (x3.id = x4.id)
                      JOIN x5 ON  (x4.id = x5.id);
…
Planning Time: 0.069 ms
Execution Time: 0.046 ms

這裏真正值得注意的是執行計劃速度的顯著提升。我們可以看到驚人的 4 倍加速。

然而，我需要提醒大家：優化器試圖重新構造連接肯定是有原因的。如果查詢成本比我們在上述示例中看到的更高，那麼投入更多時間來創建計劃就很有意義。換句話說：除非最終用戶完全瞭解發生了什麼，否則改變這個變量可能會適得其反。因此，我們建議在更改此設置之前使用真實數據和真實工作負載測試您的查詢和整個設置。一般來說，僅爲單個查詢更改變量並將 postgresql.conf 中的默認值保持不變，對於所有其他操作可能是有益的。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/2wjvMXguol5r2AKlpPriLw

猜你喜歡