本文為云貝教育 劉峰 原創(chuàng)，請尊重知識產(chǎn)權，轉(zhuǎn)發(fā)請注明出處，不接受任何抄襲、演繹和未經(jīng)注明出處的轉(zhuǎn)載。
一、介紹
在本文中，我們將了解 PostgreSQL AUTO_EXPLAIN功能的工作原理，以及為什么應該使用它來收集在生產(chǎn)系統(tǒng)上執(zhí)行的 SQL 語句的實際執(zhí)行計劃。
二、SQL執(zhí)行計劃
當您向 PostgreSQL 發(fā)送 SQL 語句時，該語句的執(zhí)行方式如下圖所示：
首先，基于文本的 SQL 語句被解析為可由數(shù)據(jù)庫服務器以編程方式遍歷的抽象語法樹（例如查詢樹）。
其次，優(yōu)化器使用查詢樹生成最佳執(zhí)行計劃，該執(zhí)行計劃消耗最少的資源來生成所需的結果。
第三，執(zhí)行器運行執(zhí)行計劃，并將輸出作為查詢結果集返回給客戶端。
三、估計執(zhí)行計劃和真實的執(zhí)行計劃
當您使用 EXPLAIN 命令時。 PostgreSQL 僅返回估計執(zhí)行計劃，即優(yōu)化器認為對于所提供的 SQL 語句最有效的計劃。但是，當您運行 EXPLAIN 命令時，該語句并未真正執(zhí)行。
另一方面，如果我們運行 EXPLAIN ANALYZE，PostgreSQL 會運行該語句，因此我們將得到實際執(zhí)行計劃，其中還包含執(zhí)行計劃中每個操作的計時信息。
在研究生產(chǎn)系統(tǒng)上的慢速查詢時，我們可能會面臨幾個挑戰(zhàn)。
? 首先，出于安全原因，我們可能不允許在生產(chǎn)系統(tǒng)上運行查詢，因此，在這種情況下，我們不能簡單地運行 EXPLAIN ANALYZE 命令來獲取實際執(zhí)行計劃。
? 其次，即使我們有權運行 EXPLAIN ANALYZE 命令，我們也可能會觀察到與客戶抱怨的計劃不同的計劃。這可能是由于幾個原因造成的。
例如，PostgreSQL 有一個prepareThreshold 設置，其默認值為5。該值告訴PostgreSQL 在切換到使用通用計劃的服務器端Prepared Statement 之前，它可以在客戶端模擬Prepared Statement 多少次。
如果慢速查詢使用通用計劃，即使運行 EXPLAIN ANALYZE（它會動態(tài)生成執(zhí)行計劃），您也可能無法獲得相同的計劃。
因此，分析慢速查詢的一個更好的解決方案是，我們獲取 PostgreSQL 在運行相關查詢時使用的實際執(zhí)行計劃。
四、Auto Explain 模塊
PostgreSQL 是非常可定制的，它提供了幾個我們可以顯式激活的擴展。
auto_explain 就是這樣的一個擴展，它允許我們捕獲花費時間超過預定義閾值的 SQL 查詢的實際執(zhí)行計劃。
auto_explain模塊提供了一種自動記錄執(zhí)行計劃的方法，而無需手動運行。這對于在大型應用程序中追蹤未優(yōu)化的查詢特別有用。
該模塊不提供SQL可訪問的功能。要使用它，只需將其加載到服務器中。您可以將其加載到單個會話中：
您必須是超級用戶才能執(zhí)行此操作。更典型的用法是通過在 postgresql.conf 中的 session_preload_libraries 中包含 auto_explain 或 shared_preload_libraries 來將其預加載到部分或所有會話中。然后，無論查詢何時發(fā)生，您都可以跟蹤異常緩慢的查詢。當然，這需要付出一定的管理費用。
4.1 安裝
支持 PostgreSQL 版本 12 及更高版本。
在繼續(xù)之前安裝 PostgreSQL。確保有 pg_config 二進制文件，它們通常包含在 -dev 和 -devel 包中。
如果下載失敗，可直接下載ZIP包上傳解壓安裝
4.2 配置參數(shù)
有幾個配置參數(shù)控制 auto_explain 的行為。請注意，默認行為是不執(zhí)行任何操作，因此如果您想要任何結果，則必須至少設置 auto_explain.log_min_duration。
auto_explain.log_min_duration（整數(shù)）
auto_explain.log_min_duration 是將導致記錄該語句的計劃的最短語句執(zhí)行時間（以毫秒為單位）。將其設置為 0 會記錄所有計劃。 -1（默認值）禁用計劃記錄。例如，如果將其設置為 250 毫秒，則將記錄運行 250 毫秒或更長的所有語句。只有超級用戶才能更改此設置。
auto_explain.log_parameter_max_length（整數(shù)）
auto_explain.log_parameter_max_length 控制查詢參數(shù)值的記錄。值為 -1（默認值）會完整記錄參數(shù)值。 0 禁用參數(shù)值記錄。大于零的值會將每個參數(shù)值截斷為那么多字節(jié)。只有超級用戶才能更改此設置。
auto_explain.log_analyze（布爾值）
auto_explain.log_analyze 會導致在記錄執(zhí)行計劃時打印 EXPLAIN ANALYZE 輸出，而不僅僅是 EXPLAIN 輸出。該參數(shù)默認關閉。只有超級用戶才能更改此設置。
注意：當此參數(shù)打開時，所有執(zhí)行的語句都會按計劃節(jié)點計時，無論它們運行的時間是否足夠長以實際記錄日志。這可能會對性能產(chǎn)生極其負面的影響。關閉 auto_explain.log_timing 可改善性能成本，但代價是獲取的信息較少。
auto_explain.log_buffers（布爾值）
auto_explain.log_buffers 控制在記錄執(zhí)行計劃時是否打印緩沖區(qū)使用統(tǒng)計信息；它相當于 EXPLAIN 的 BUFFERS 選項。除非啟用 auto_explain.log_analyze，否則此參數(shù)無效。該參數(shù)默認關閉。只有超級用戶才能更改此設置。
auto_explain.log_wal（布爾值）
auto_explain.log_wal 控制在記錄執(zhí)行計劃時是否打印 WAL 使用統(tǒng)計信息；它相當于 EXPLAIN 的 WAL 選項。除非啟用 auto_explain.log_analyze，否則此參數(shù)無效。該參數(shù)默認關閉。只有超級用戶才能更改此設置。
auto_explain.log_timing（布爾值）
auto_explain.log_timing 控制在記錄執(zhí)行計劃時是否打印每個節(jié)點的計時信息；它相當于 EXPLAIN 的 TIMING 選項。重復讀取系統(tǒng)時鐘的開銷可能會顯著減慢某些系統(tǒng)上的查詢速度，因此當僅需要實際行計數(shù)而不是精確時間時，將此參數(shù)設置為關閉可能很有用。除非啟用 auto_explain.log_analyze，否則此參數(shù)無效。該參數(shù)默認開啟。只有超級用戶才能更改此設置。
auto_explain.log_triggers（布爾值）
auto_explain.log_triggers 導致在記錄執(zhí)行計劃時包含觸發(fā)器執(zhí)行統(tǒng)計信息。除非啟用 auto_explain.log_analyze，否則此參數(shù)無效。該參數(shù)默認關閉。只有超級用戶才能更改此設置。
auto_explain.log_verbose（布爾值）
auto_explain.log_verbose 控制在記錄執(zhí)行計劃時是否打印詳細信息；它相當于 EXPLAIN 的 VERBOSE 選項。該參數(shù)默認關閉。只有超級用戶才能更改此設置。
auto_explain.log_settings（布爾值）
auto_explain.log_settings 控制在記錄執(zhí)行計劃時是否打印有關修改的配置選項的信息。輸出中僅包含影響查詢計劃且其值與內(nèi)置默認值不同的選項。該參數(shù)默認關閉。只有超級用戶才能更改此設置。
auto_explain.log_format（枚舉）
auto_explain.log_format 選擇要使用的 EXPLAIN 輸出格式。允許的值為 text、xml、json 和 yaml。默認為文本。只有超級用戶才能更改此設置。
auto_explain.log_level（枚舉）
auto_explain.log_level 選擇 auto_explain 將記錄查詢計劃的日志級別。有效值為 DEBUG5、DEBUG4、DEBUG3、DEBUG2、DEBUG1、INFO、NOTICE、WARNING 和 LOG。默認為日志。只有超級用戶才能更改此設置。
auto_explain.log_nested_statements（布爾值）
auto_explain.log_nested_statements 導致考慮記錄嵌套語句（在函數(shù)內(nèi)執(zhí)行的語句）。當它關閉時，僅記錄頂級查詢計劃。該參數(shù)默認關閉。只有超級用戶才能更改此設置。
auto_explain.sample_rate（實數(shù)）
auto_explain.sample_rate 導致 auto_explain 僅解釋每個會話中的一小部分語句。默認值為 1，表示解釋所有查詢。如果是嵌套語句，則要么全部解釋，要么不解釋。只有超級用戶才能更改此設置。
在日常使用中，這些參數(shù)是在 postgresql.conf 中設置的，盡管超級用戶可以在自己的會話中即時更改它們。典型用法可能是：
4.3 示例
1）創(chuàng)建擴展
2）窗口1執(zhí)行以下查詢
3)窗口2查看當前正在執(zhí)行的SQL
可以看到PID=43144的進程，正在執(zhí)行SQL：select pg_sleep(3),relname from pg_class;
執(zhí)行計劃為全表掃描，并且該真實的執(zhí)行計劃不需要通過explain analyze獲取。
想要了解更多關于數(shù)據(jù)庫文章請＋sqltuning
