許多人已經在使用 - 我們的 PostgreSQL 計劃視覺化服務可能不知道它的超能力之一 - 轉動伺服器日誌中難以閱讀的部分...
....到一個設計精美的查詢，其中包含相應計劃節點的上下文提示：
在他的第二部分的記錄中 我會告訴你我們是如何做到這一點的。
第一部分的記錄，專門討論典型的查詢效能問題及其解決方案，可以在文章中找到 .
首先，讓我們開始著色 - 我們將不再為計劃著色，我們已經為它著色，我們已經擁有它美麗且易於理解的，但一個請求。
在我們看來，使用這樣一個未格式化的“表”，從日誌中提取的請求看起來非常難看，因此不方便。
特別是當開發人員將程式碼中的請求正文「黏合」在一行中時（當然，這是一種反模式，但它確實發生了）。 可怕！
讓我們把它畫得更漂亮一些。
如果我們能把它畫得很漂亮，也就是說，將請求的主體拆散並重新組合在一起，那麼我們就可以給這個請求的每個對象“附加”一個提示——計劃中相應點發生了什麼。
查詢語法樹
為此，必須先解析請求。
因為我們有 ，然後我們給它做了一個模組，你可以 。 事實上，這些是對 PostgreSQL 解析器本身內部的擴充「綁定」。 也就是說，語法只是二進制編譯的，並且從 NodeJS 對其進行綁定。 我們以其他人的模組為基礎——這裡沒有什麼大秘密。
我們將請求正文作為函數的輸入 - 在輸出中，我們得到 JSON 物件形式的已解析語法樹。
現在我們可以以相反的方向遍歷這棵樹，並使用我們想要的縮排、顏色和格式來組合請求。 不，這不是可自訂的，但在我們看來這會很方便。
映射查詢和計劃節點
現在讓我們看看如何將第一步分析的計劃和第二步分析的查詢結合起來。
讓我們舉一個簡單的例子 - 我們有一個產生 CTE 並從中讀取兩次的查詢。 他制定了這樣一個計劃。
CTE
如果你仔細看一下，直到版本 12（或從它開始使用關鍵字 MATERIALIZED) 形成 .
這意味著，如果我們在請求中的某處看到 CTE 生成，並且在計劃中的某處看到節點 CTE，那麼這些節點肯定是互相「打架」的，我們可以立即將它們組合起來。
星號的問題：CTE 可以嵌套。
其中有嵌套很差的，甚至有同名的。 例如，您可以在裡面 CTE A 使 CTE X，並且在同一水平內部 CTE B 再來一遍 CTE X:
WITH A AS (
WITH X AS (...)
SELECT ...
)
, B AS (
WITH X AS (...)
SELECT ...
)
...對比的時候你一定要明白這一點。 「用眼睛」理解這一點——甚至看到計劃，甚至看到請求的正文——是非常困難的。 如果你的 CTE 生成是複雜的、嵌套的，而且請求很大，那麼它是完全無意識的。
UNION
如果我們在查詢中有一個關鍵字 UNION [ALL] （連接兩個樣本的運算子），那麼在計劃中它對應於一個節點 Append，或一些 Recursive Union.
上面的“上面” UNION - 這是我們節點的第一個後代，它位於“下面” - 第二個。 如果透過 UNION 我們同時「黏」了幾個塊，然後 Append-仍然只有一個節點，但它不會有兩個，而是許多子節點 - 按照它們的順序分別：
(...) -- #1
UNION ALL
(...) -- #2
UNION ALL
(...) -- #3Append
-> ... #1
-> ... #2
-> ... #3
星號的問題：內部遞歸採樣生成（WITH RECURSIVE) 也可以是多個 UNION。 但只有最後一個區塊之後的最後一個區塊總是遞歸的 UNION。 以上一切都是一個，但又有所不同 UNION:
WITH RECURSIVE T AS(
(...) -- #1
UNION ALL
(...) -- #2, тут кончается генерация стартового состояния рекурсии
UNION ALL
(...) -- #3, только этот блок рекурсивный и может содержать обращение к T
)
...
您還需要能夠“突出”此類範例。 在這個例子中我們看到 UNION-我們的請求中有 3 個部分。 據此，一 UNION 對應於 Append-節點，以及另一個 - Recursive Union.
讀寫數據
一切都已安排好，現在我們知道請求的哪一部分對應於計劃的哪一部分。 而在這些作品中，我們可以輕鬆、自然地找到那些「可讀」的物件。
從查詢的角度來看，我們不知道它是表格還是CTE，但它們是由同一個節點指定的 RangeVar。 就「可讀性」而言，這也是一組相當有限的節點：
Seq Scan on [tbl]Bitmap Heap Scan on [tbl]Index [Only] Scan [Backward] using [idx] on [tbl]CTE Scan on [cte]Insert/Update/Delete on [tbl]
我們知道計劃和查詢的結構，我們知道區塊的對應關係，我們知道物件的名稱 - 我們進行一對一的比較。
再次 任務“帶星號”。 我們接受請求，執行它，我們沒有任何別名 - 我們只是從同一個 CTE 讀取它兩次。
我們來看看這個計畫──問題出在哪裡？ 為什麼我們有別名？ 我們沒有訂購。 他從哪裡得到這樣的「號碼」？
PostgreSQL 自己加入它。 你只需要明白這一點 只是這樣一個別名 對我們來說，為了與計劃進行比較，它沒有任何意義，只是在這裡添加。 我們不要關注他。
第二個 任務“帶星號”：如果我們從分區表中讀取，那麼我們將得到一個節點 Append 或 Merge Append，它將由大量的“孩子”組成，並且每個孩子都會以某種方式 Scan'om 來自表部分： Seq Scan, Bitmap Heap Scan 或 Index Scan。 但是，無論如何，這些「子節點」都不會是複雜的查詢——這就是這些節點與其他節點的區別 Append 在 UNION.
我們也了解這樣的結，將它們「堆成一堆」並說：「你從 megatable 讀到的所有內容都在這裡，在樹下".
「簡單」的資料接收節點
Values Scan 對應於計劃 VALUES 在請求中。
Result 是一個沒有的請求 FROM 有點 SELECT 1。 或當你有故意虛假的表達 WHERE-block（然後屬性出現 One-Time Filter):
EXPLAIN ANALYZE
SELECT * FROM pg_class WHERE FALSE; -- или 0 = 1Result (cost=0.00..0.00 rows=0 width=230) (actual time=0.000..0.000 rows=0 loops=1)
One-Time Filter: false
Function Scan 「映射」到同名的 SRF。
但對於嵌套查詢，一切都變得更加複雜 - 不幸的是，它們並不總是變成 InitPlan/SubPlan。 有時他們會變成 ... Join 或 ... Anti Join，尤其是當你寫類似的東西時 WHERE NOT EXISTS ...。 在這裡，並不總是可以將它們組合起來 - 在計劃的文本中，沒有與計劃的節點相對應的運算符。
再次 任務“帶星號”： 一些 VALUES 在請求中。 在這種情況下和計劃中，您將獲得多個節點 Values Scan.
「編號」後綴將有助於區分它們 - 它們是按照找到相應後綴的順序精確添加的 VALUES-從上到下阻止請求。
資料處理
看來我們的請求中的所有內容都已解決 - 剩下的就是 Limit.
但這裡一切都很簡單 - 例如節點 Limit, Sort, Aggregate, WindowAgg, Unique 一對一地「映射」到請求中對應的運算子（如果存在）。 這裡沒有“明星”或困難。
註冊
當我們想要結合時就會遇到困難 JOIN 他們之間。 這並不總是可能的，但它是可能的。
從查詢解析器的角度來看，我們有一個節點 JoinExpr，它正好有兩個孩子 - 左和右。 因此，這就是請求中 JOIN「上方」和其「下方」所寫的內容。
從計劃的角度來看，這是某些人的兩個後代 * Loop/* Join-節點。 Nested Loop, Hash Anti Join,... - 類似的東西。
讓我們使用簡單的邏輯：如果我們有表 A 和 B 在計劃中相互“連接”，那麼在請求中它們可以位於 A-JOIN-B或 B-JOIN-A。 讓我們嘗試以這種方式組合，讓我們嘗試以相反的方式組合，依此類推，直到我們用完這樣的對。
讓我們看看我們的語法樹，看看我們的計劃，看看它們......不相似！
讓我們以圖表的形式重新繪製它——哦，它已經看起來像什麼了！
請注意，我們的節點同時具有子節點 B 和 C - 我們不關心以什麼順序。 我們把它們組合起來，把節點的圖片翻過來。
我們再看一下。 現在我們有了帶有子 A 和對 (B + C) 的節點 - 也與它們相容。
偉大的！ 原來我們就是這兩個人 JOIN 來自請求與計劃節點已成功合併。
唉，這個問題並不總是能解決。
例如，如果在請求中 A JOIN B JOIN C，在計劃中，首先連接了「外部」節點A和C，但是請求中沒有這樣的操作符，我們沒有什麼可以強調的，沒有什麼可以附加提示的。 跟你寫的時候的「逗號」是一樣的 A, B.
但是，在大多數情況下，幾乎所有節點都可以“解開”，並且您可以及時獲得左側的這種分析 - 從字面上看，就像在 Google Chrome 中分析 JavaScript 程式碼時一樣。 您可以看到每行和每條語句「執行」所需的時間。
為了讓您更方便地使用這一切，我們做了存儲 ，您可以在其中保存並稍後查找您的計劃以及相關的請求，或與某人共享連結。
如果您只需要將不可讀的查詢轉換為適當的形式，請使用 .
來源： www.habr.com