更方便地了解 PostgreSQL 查詢計劃

半年前 我們介紹了 解釋.tensor.ru - 民眾 用於解析和可視化查詢計劃的服務 到 PostgreSQL。

在過去的幾個月裡，我們已經對他做了 PGConf.Russia 2020 報告, 準備了一個總結 關於加速 SQL 查詢的文章 基於它給出的建議……但最重要的是，我們收集了您的反饋並研究了真實的用例。

現在我們準備告訴您可以使用的新功能。

支持不同的計劃格式

根據日誌計劃以及請求

直接從控制台，我們選擇整個塊，從行開始 查詢文本，所有前導空格：

        Query Text: INSERT INTO  dicquery_20200604  VALUES ($1.*) ON CONFLICT (query)
                           DO NOTHING;
        Insert on dicquery_20200604  (cost=0.00..0.05 rows=1 width=52) (actual time=40.376..40.376 rows=0 loops=1)
          Conflict Resolution: NOTHING
          Conflict Arbiter Indexes: dicquery_20200604_pkey
          Tuples Inserted: 1
          Conflicting Tuples: 0
          Buffers: shared hit=9 read=1 dirtied=1
          ->  Result  (cost=0.00..0.05 rows=1 width=52) (actual time=0.001..0.001 rows=1 loops=1)

...並將所有復制的內容直接放入計劃的字段中，不分離任何內容：

在輸出處，我們也得到了拆解計劃的加成 上下文選項卡，我們的要求在這裡得到了充分的體現：

JSON 和 YAML

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM pg_class;

"[
  {
    "Plan": {
      "Node Type": "Seq Scan",
      "Parallel Aware": false,
      "Relation Name": "pg_class",
      "Alias": "pg_class",
      "Startup Cost": 0.00,
      "Total Cost": 1336.20,
      "Plan Rows": 13804,
      "Plan Width": 539,
      "Actual Startup Time": 0.006,
      "Actual Total Time": 1.838,
      "Actual Rows": 10266,
      "Actual Loops": 1,
      "Shared Hit Blocks": 646,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0
    },
    "Planning Time": 5.135,
    "Triggers": [
    ],
    "Execution Time": 2.389
  }
]"

即使有外部引號，如 pgAdmin 副本，即使沒有 - 我們在同一個字段中拋出，輸出也是美麗的：

高級可視化

計劃時間/執行時間

現在您可以更好地了解執行查詢時額外的時間去了哪裡：

輸入輸出時序

有時你不得不處理這樣一種情況，就資源而言，似乎沒有太多的讀取和寫入，但由於某種原因執行時間似乎不協調。

這裡不得不說：哦，可能是那個時候服務器上的磁盤負載太大了，才讀了這麼久！“但不知何故，它不是很準確......

但它可以絕對可靠地確定。 事實上，在 PG 服務器的配置選項中有 track_io_timing:

啟用定時 I/O 操作。 默認情況下禁用此設置，因為它需要操作系統不斷查詢當前時間，這會在某些平台上顯著降低速度。 您可以使用 pg_test_timing 實用程序來估計平台上的計時開銷。 I/O統計信息可以通過pg_stat_database視圖獲取， 在 EXPLAIN 輸出中（使用 BUFFERS 參數時） 並通過 pg_stat_statements 視圖。

也可以在本地會話中啟用此選項：

SET track_io_timing = TRUE;

好吧，現在最好的部分是我們已經學會了理解和顯示這些數據，同時考慮到執行樹的所有轉換：

在這裡您可以看到，在總執行時間的 0.790 毫秒中，讀取一頁數據用了 0.718 毫秒，寫入數據用了 0.044 毫秒，而所有其他有用的活動只用了 0.028 毫秒！

PostgreSQL 13 的未來

有關新增功能的完整概述，請參閱 在一篇詳細的文章中，我們專門討論計劃的變化。

規劃緩衝區

分配給調度程序的資源的計算反映在另一個與 pg_stat_statements 無關的補丁中。 使用 BUFFERS 選項的 EXPLAIN 將報告規劃階段使用的緩衝區數量：

 Seq Scan on pg_class (actual rows=386 loops=1)
   Buffers: shared hit=9 read=4
 Planning Time: 0.782 ms
   Buffers: shared hit=103 read=11
 Execution Time: 0.219 ms

增量排序

在需要按多個鍵（k1、k2、k3……）排序的情況下，規劃器現在可以利用知道數據已經按幾個第一個鍵（例如 k1 和 k2）排序的信息。 在這種情況下，你不能將所有數據重新排序，而是將它們分成具有相同k1和k2值的連續組，並通過關鍵字k3對它們進行“重新排序”。

因此，整個排序分解為幾個連續的較小尺寸的排序。 這減少了所需的內存量，還允許您在所有排序完成之前返回第一個數據。

 Incremental Sort (actual rows=2949857 loops=1)
   Sort Key: ticket_no, passenger_id
   Presorted Key: ticket_no
   Full-sort Groups: 92184 Sort Method: quicksort Memory: avg=31kB peak=31kB
   ->  Index Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)
 Planning Time: 2.137 ms
 Execution Time: 2230.019 ms

用戶界面/用戶體驗改進

截圖無處不在！

現在在每個選項卡上都有機會快速 將選項卡的屏幕截圖保存到剪貼板 對於選項卡的整個寬度和深度 - “視線”右上角:

實際上，本刊的大部分圖片都是通過這種方式獲得的。

關於節點的建議

不僅數量更多，而且幾乎每一個都可以 詳細閱讀文章通過以下鏈接：

從存檔中刪除

有些人要求能夠 刪除“絕對” 即使是未在存檔中發布的計劃 - 請單擊相應的圖標：

好吧，別忘了我們有 支援團隊您可以在其中寫下您的意見和建議。

來源： www.habr.com