🥇EXPLAIN 保持沉默的原因以及如何讓它開口說話

開發人員向 DBA 詢問或企業主向 PostgreSQL 顧問詢問的經典問題幾乎總是相同的： “為什麼查詢在資料庫上運行需要這麼長時間？”

傳統原因：

低效演算法
當你決定對數萬筆記錄進行 JOIN 操作時
過時的統計數據
如果表中資料的實際分佈與上次 ANALYZE 收集的資料有很大不同
資源“停擺”
專用 CPU 運算能力不足，需要不斷抽取 GB 的內存，或磁碟無法滿足資料庫的所有“需求”
阻塞來自競爭過程

如果堵塞很難發現和分析，那麼對於其他一切，我們只需要 查詢計劃，可以使用 EXPLAIN 運算符 (當然，最好立即解釋（分析、緩衝）…）或 auto_explain模組.

但是，正如同一文獻所述，

“理解計劃是一門藝術，要掌握它需要一定的經驗，...”

但如果您使用正確的工具，那麼沒有它您也可以！

查詢計劃通常是什麼樣的？像這樣：

Index Scan using pg_class_relname_nsp_index on pg_class (actual time=0.049..0.050 rows=1 loops=1)
  Index Cond: (relname = $1)
  Filter: (oid = $0)
  Buffers: shared hit=4
  InitPlan 1 (returns $0,$1)
    ->  Limit (actual time=0.019..0.020 rows=1 loops=1)
          Buffers: shared hit=1
          ->  Seq Scan on pg_class pg_class_1 (actual time=0.015..0.015 rows=1 loops=1)
                Filter: (relkind = 'r'::"char")
                Rows Removed by Filter: 5
                Buffers: shared hit=1

或者像這樣：

"Append  (cost=868.60..878.95 rows=2 width=233) (actual time=0.024..0.144 rows=2 loops=1)"
"  Buffers: shared hit=3"
"  CTE cl"
"    ->  Seq Scan on pg_class  (cost=0.00..868.60 rows=9972 width=537) (actual time=0.016..0.042 rows=101 loops=1)"
"          Buffers: shared hit=3"
"  ->  Limit  (cost=0.00..0.10 rows=1 width=233) (actual time=0.023..0.024 rows=1 loops=1)"
"        Buffers: shared hit=1"
"        ->  CTE Scan on cl  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.021..0.021 rows=1 loops=1)"
"              Buffers: shared hit=1"
"  ->  Limit  (cost=10.00..10.10 rows=1 width=233) (actual time=0.117..0.118 rows=1 loops=1)"
"        Buffers: shared hit=2"
"        ->  CTE Scan on cl cl_1  (cost=0.00..997.20 rows=9972 width=233) (actual time=0.001..0.104 rows=101 loops=1)"
"              Buffers: shared hit=2"
"Planning Time: 0.634 ms"
"Execution Time: 0.248 ms"

但是從表格中讀取文字計劃非常困難，而且不直觀：

在節點中顯示 子樹資源總和
也就是說，要了解執行特定節點需要多少時間，或從表中提取多少數據，你需要以某種方式從另一個中減去一個
節點所需時間 乘以循環
是的，減法並不是最難的「在腦中」完成的運算——畢竟，執行時間是節點執行一次的平均值，而節點執行次數可能有數百次。
好吧，所有這些加在一起阻止我們回答主要問題——那麼誰 “最薄弱的環節”?

當我們嘗試向數百名開發人員解釋這一切時，我們意識到從外部來看它看起來像這樣：

這意味著我們需要...

工具

在其中，我們嘗試收集所有關鍵機制，以便根據計劃和要求，理解「誰應該受到指責以及該做什麼」。好吧，和社區分享一些我的經驗。
相聚並享受— 解釋.tensor.ru

計劃的可見性

當計劃看起來像這樣時，它是否容易理解？

Seq Scan on pg_class (actual time=0.009..1.304 rows=6609 loops=1)
  Buffers: shared hit=263
Planning Time: 0.108 ms
Execution Time: 1.800 ms

不好。

但事實就是這樣， 縮寫形式，當關鍵指標分開來的時候，就已經清晰多了：

但如果計劃更複雜，就會有人來幫忙 圓餅圖時間分佈 按節點：

好吧，對於最困難的選擇，它可以拯救你 執行圖:

例如，在相當重要的情況下，一個計劃可能具有多個事實根：

結構線索

好吧，如果計劃的整個結構及其弱點已經佈置好並且可見，為什麼不向開發人員強調它們並用“俄語”解釋它們呢？

我們已經收集了幾十個這樣的推薦範本。

逐行查詢分析器

現在，如果將原始查詢覆蓋在正在分析的計劃上，您可以看到每個單獨的操作符花費了多少時間 - 如下所示：

……或者像這樣：

將參數替換到查詢中

如果您不僅將查詢附加到計劃，而且還將日誌 DETAIL 行中的參數附加到計劃，則可以在以下選項之一中另外複製它：

將值替換到查詢中
直接在您自己的基礎上實施並進一步分析
```
SELECT 'const', 'param'::text;
```
透過 PREPARE/EXECUTE 替換值
模擬調度程序的工作，當參數部分可以忽略時 - 例如，在分區表上工作時
```
DEALLOCATE ALL;
PREPARE q(text) AS SELECT 'const', $1::text;
EXECUTE q('param'::text);
```

計劃檔案

插入、分析、與同事分享！這些計劃將保留在檔案中，您可以稍後返回： explain.tensor.ru/archive

但如果您不想讓其他人看到您的計劃，請不要忘記勾選「不在檔案中發布」方塊。

在接下來的文章中，我將討論分析計畫時所出現的挑戰和解決方案。

來源： www.habr.com

EXPLAIN 沒有說明什麼，如何讓它說明

工具

計劃的可見性

結構線索

逐行查詢分析器

將參數替換到查詢中

計劃檔案