fbpx

PostgreSQL 15 auto_explain 功能強化：讓普通帳號也能查看 SQL 執行計畫

EDB 技術專欄 PostgreSQL 技術專欄 2024 年 4 月 16 日 最後更新時間 : 2024 年 4 月 19 日
渡鴉之丘 資料庫技術顧問 歐立威科技

看得到用不到的 auto_explain

在 PostgreSQL 的設計中，使用 PL/pgSQL 寫的 stored procedure 與 user-defined function，是一種黑箱的設計機制。通常不易直接看到裡面的運作，需要透過 auto_explain 才能間接查看。

但一直以來，auto_explain 是一個看得到用不到的功能：

  1. 用 auto_explain 捕捉 stored function/procedure 裡面的 SQL 執行計畫，會紀錄在 DB log 裡面。不過，通常寫程式的人在公司裡不見得能夠直接登入 DB 主機查看資料庫 log。
  1. auto_explain 限定 superuser 使用

用 superuser 查看 stored procedure 內的執行計畫，其實不太實際，除了工作職責分離，在共用環境使用 superuser 可能會有誤觸的危險。

好在以上困難點在 EDB/PostgreSQL 15 開始妥善的得到解決～

EDB/PostgreSQL 15 改善 auto_explain 功能

在 EDB/PostgreSQL 15 開始，可以讓我們完整使用 auto_explain 功能～～

  1. 我們可以把 log 訊息往前端送：指定 client_min_message 參數，可以讓前端程式皆收到的 message 跟 DB log 一樣
  2. 在 PostgreSQL 15 多了允許指定帳號執行某些參數 set 的權限了～

在全資料庫指定 auto_explain.log_min_duration = -1 預設不自動抓，以免影響效能，需要時具備權限帳號的個別 session 自己指定 auto_explain.log_min_duration = 0。

還有其他的幾個參數，基本上就是 EXPLAIN 的各種參數的對應開關，顯示的東西會更細，這邊就不特別弄了。

這邊特別設定的是槽狀內容的執行計畫顯示，以便達到主要目標（查看 stored procedure 內容）。

[enterprisedb@edb15 ~]$ psql -d edb
Null display is "(NULL)".
Timing is on.
psql (15.6.0)
Type "help" for help.

[[local]] edb=# ALTER SYSTEM SET session_preload_libraries = '$libdir/auto_explain';
ALTER SYSTEM
Time: 21.323 ms
[[local]] edb=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Time: 0.293 ms
[[local]] edb=#
[[local]] edb=# ALTER SYSTEM SET auto_explain.log_min_duration = -1;
ALTER SYSTEM
Time: 34.568 ms
[[local]] edb=# ALTER SYSTEM SET auto_explain.log_analyze = true;
ALTER SYSTEM
Time: 32.499 ms
[[local]] edb=# ALTER SYSTEM SET auto_explain.log_nested_statements = true;
ALTER SYSTEM
Time: 23.925 ms
[[local]] edb=# select pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

Time: 0.580 ms
[[local]] edb=#

為了測試，這邊準備一支沒什麼用處的範例 stored function：裡面只是用三種方式呼叫 SQL

create or replace function test_debugger_explain() 
returns void AS $xx$
declare
 testvar int := 3;
begin
 perform * from pg_stat_activity;
 select count(*) into testvar from pg_stat_activity;
 execute $$select * from pg_stat_activity$$;
end;
$xx$ language plpgsql;

[enterprisedb@edb15 ~]$ psql -d edb
Null display is "(NULL)".
Timing is on.
psql (15.6.0)
Type "help" for help.

[[local]] edb=# create or replace function test_debugger_explain()
returns void AS $xx$
declare
 testvar int := 3;
begin
 perform * from pg_stat_activity;
 select count(*) into testvar from pg_stat_activity;
 execute $$select * from pg_stat_activity$$;
end;
$xx$ language plpgsql;
CREATE FUNCTION
Time: 112.805 ms
[[local]] edb=# grant execute on function test_debugger_explain to aaa;
GRANT
Time: 10.309 ms
[[local]] edb=#

順便筆記一下，因為資安意識強化，在 PostgreSQL 15 開始，public schema 也不能讓隨意人放東西了

[[local]] edb=> create or replace function test_debugger_explain()
returns void AS $xx$
declare
 testvar int := 3;
begin
 perform * from pg_stat_activity;
 select count(*) into testvar from pg_stat_activity;
 execute $$select * from pg_stat_activity$$;
end;
$xx$ language plpgsql;
ERROR: permission denied for schema public
Time: 22.630 ms
[[local]] edb=>

啟用模組提供普通帳號執行：目標是讓普通帳號只要執行 set 即可立即啟用

但是！在預設狀況下，普通帳號不能跑這個 set

[[local]] edb=# \du
                                                     List of roles
       Role name       |                         Attributes                         |             Member of
-----------------------+------------------------------------------------------------+-----------------------------------
 aaa                   | Profile default                                            | {}
 aq_administrator_role | No inheritance, Cannot login                              +| {}
                       | Profile default                                            |
 enterprisedb          | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}
                       | Profile default                                            |
 pemagent              | Profile default                                            | {pg_monitor,pg_read_all_settings}

[[local]] edb=#
[[local]] edb=# \c edb aaa
You are now connected to database "edb" as user "aaa".
[[local]] edb=> SET auto_explain.log_min_duration = 0;
ERROR:  permission denied to set parameter "auto_explain.log_min_duration"
Time: 10.797 ms
[[local]] edb=>

允許 non-superuser 操作，需要有在 PostgreSQL 15 可以對參數 GRANT SET ON PARAMETER 的功能。

[[local]] edb=# GRANT SET ON PARAMETER auto_explain.log_min_duration TO aaa ;
GRANT
Time: 26.979 ms
[[local]] edb=#

這樣就能順利讓普通帳號使用～另外為了 client_min_messages 要到 log 才行

[[local]] edb=# \c edb aaa
You are now connected to database "edb" as user "aaa".
[[local]] edb=> show auto_explain.log_level;
 auto_explain.log_level
------------------------
 log
(1 row)

Time: 0.192 ms
[[local]] edb=>
[[local]] edb=> SET auto_explain.log_min_duration = 0;
SET
Time: 0.365 ms
[[local]] edb=> SET client_min_messages TO log ;
SET
Time: 0.166 ms
[[local]] edb=> 
[[local]] edb=> SELECT  test_debugger_explain();
LOG:  duration: 0.231 ms  plan:
Query Text: SELECT * from pg_stat_activity
Hash Left Join  (cost=2.36..3.91 rows=100 width=452) (actual time=0.207..0.220 rows=9 loops=1)
  Hash Cond: (s.usesysid = u.oid)
  ->  Hash Left Join  (cost=1.05..2.32 rows=100 width=388) (actual time=0.169..0.177 rows=9 loops=1)
        Hash Cond: (s.datid = d.oid)
        ->  Function Scan on pg_stat_get_activity s  (cost=0.00..1.00 rows=100 width=324) (actual time=0.118..0.119 rows=9 loops=1)
        ->  Hash  (cost=1.02..1.02 rows=2 width=68) (actual time=0.027..0.027 rows=4 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              ->  Seq Scan on pg_database d  (cost=0.00..1.02 rows=2 width=68) (actual time=0.013..0.014 rows=4 loops=1)
  ->  Hash  (cost=1.14..1.14 rows=14 width=68) (actual time=0.017..0.017 rows=16 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 10kB
        ->  Seq Scan on pg_authid u  (cost=0.00..1.14 rows=14 width=68) (actual time=0.004..0.007 rows=16 loops=1)
LOG:  duration: 0.031 ms  plan:
Query Text: select count(*)              from pg_stat_activity
Aggregate  (cost=1.25..1.26 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=1)
  ->  Function Scan on pg_stat_get_activity s  (cost=0.00..1.00 rows=100 width=0) (actual time=0.023..0.024 rows=9 loops=1)
LOG:  duration: 0.102 ms  plan:
Query Text: select * from pg_stat_activity
Hash Left Join  (cost=2.36..3.91 rows=100 width=452) (actual time=0.079..0.092 rows=9 loops=1)
  Hash Cond: (s.usesysid = u.oid)
  ->  Hash Left Join  (cost=1.05..2.32 rows=100 width=388) (actual time=0.055..0.063 rows=9 loops=1)
        Hash Cond: (s.datid = d.oid)
        ->  Function Scan on pg_stat_get_activity s  (cost=0.00..1.00 rows=100 width=324) (actual time=0.024..0.026 rows=9 loops=1)
        ->  Hash  (cost=1.02..1.02 rows=2 width=68) (actual time=0.017..0.018 rows=4 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              ->  Seq Scan on pg_database d  (cost=0.00..1.02 rows=2 width=68) (actual time=0.009..0.010 rows=4 loops=1)
  ->  Hash  (cost=1.14..1.14 rows=14 width=68) (actual time=0.016..0.016 rows=16 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 10kB
        ->  Seq Scan on pg_authid u  (cost=0.00..1.14 rows=14 width=68) (actual time=0.005..0.008 rows=16 loops=1)
LOG:  duration: 6.606 ms  plan:
Query Text: SELECT  test_debugger_explain();
Result  (cost=0.00..0.26 rows=1 width=4) (actual time=6.596..6.597 rows=1 loops=1)
 test_debugger_explain
-----------------------

(1 row)

Time: 8.366 ms
[[local]] edb=> 
[test_edb15] edb=> EXPLAIN SELECT  test_debugger_explain();
LOG:  duration: 0.000 ms  plan:
Query Text: EXPLAIN SELECT  test_debugger_explain();
Result  (cost=0.00..0.26 rows=1 width=4)
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.26 rows=1 width=4)
(1 row)

Time: 14.875 ms
[test_edb15] edb=>

最後，到 DB Log 查看，也看得到一樣的結果～

[enterprisedb@edb15 ~]$ cat as15/data/log/edb-2024-03-31_000000.log
...前略
2024-03-31 14:06:05 UTC LOG:  duration: 0.231 ms  plan:                                                                         Query Text: SELECT * from pg_stat_activity                                                                              Hash Left Join  (cost=2.36..3.91 rows=100 width=452) (actual time=0.207..0.220 rows=9 loops=1)                            Hash Cond: (s.usesysid = u.oid)                                                                                         ->  Hash Left Join  (cost=1.05..2.32 rows=100 width=388) (actual time=0.169..0.177 rows=9 loops=1)                            Hash Cond: (s.datid = d.oid)                                                                                            ->  Function Scan on pg_stat_get_activity s  (cost=0.00..1.00 rows=100 width=324) (actual time=0.118..0.119 rows=9 loops=1)                                                                                                                     ->  Hash  (cost=1.02..1.02 rows=2 width=68) (actual time=0.027..0.027 rows=4 loops=1)                                         Buckets: 1024  Batches: 1  Memory Usage: 9kB                                                                            ->  Seq Scan on pg_database d  (cost=0.00..1.02 rows=2 width=68) (actual time=0.013..0.014 rows=4 loops=1)                                                                                                                          ->  Hash  (cost=1.14..1.14 rows=14 width=68) (actual time=0.017..0.017 rows=16 loops=1)                                       Buckets: 1024  Batches: 1  Memory Usage: 10kB                                                                           ->  Seq Scan on pg_authid u  (cost=0.00..1.14 rows=14 width=68) (actual time=0.004..0.007 rows=16 loops=1)
2024-03-31 14:06:05 UTC CONTEXT:  SQL statement "SELECT * from pg_stat_activity"
        PL/pgSQL function test_debugger_explain() line 5 at PERFORM
2024-03-31 14:06:05 UTC LOG:  duration: 0.031 ms  plan:
        Query Text: select count(*)              from pg_stat_activity
        Aggregate  (cost=1.25..1.26 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=1)
          ->  Function Scan on pg_stat_get_activity s  (cost=0.00..1.00 rows=100 width=0) (actual time=0.023..0.024 rows=9 loops=1)
2024-03-31 14:06:05 UTC CONTEXT:  SQL statement "select count(*)              from pg_stat_activity"
        PL/pgSQL function test_debugger_explain() line 6 at SQL statement
2024-03-31 14:06:05 UTC LOG:  duration: 0.102 ms  plan:
        Query Text: select * from pg_stat_activity
        Hash Left Join  (cost=2.36..3.91 rows=100 width=452) (actual time=0.079..0.092 rows=9 loops=1)
          Hash Cond: (s.usesysid = u.oid)
          ->  Hash Left Join  (cost=1.05..2.32 rows=100 width=388) (actual time=0.055..0.063 rows=9 loops=1)
                Hash Cond: (s.datid = d.oid)
                ->  Function Scan on pg_stat_get_activity s  (cost=0.00..1.00 rows=100 width=324) (actual time=0.024..0.026 rows=9 loops=1)
                ->  Hash  (cost=1.02..1.02 rows=2 width=68) (actual time=0.017..0.018 rows=4 loops=1)
                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
                      ->  Seq Scan on pg_database d  (cost=0.00..1.02 rows=2 width=68) (actual time=0.009..0.010 rows=4 loops=1)
          ->  Hash  (cost=1.14..1.14 rows=14 width=68) (actual time=0.016..0.016 rows=16 loops=1)
                Buckets: 1024  Batches: 1  Memory Usage: 10kB
                ->  Seq Scan on pg_authid u  (cost=0.00..1.14 rows=14 width=68) (actual time=0.005..0.008 rows=16 loops=1)
2024-03-31 14:06:05 UTC CONTEXT:  SQL statement "select * from pg_stat_activity"
        PL/pgSQL function test_debugger_explain() line 7 at EXECUTE
2024-03-31 14:06:05 UTC LOG:  duration: 6.606 ms  plan:
        Query Text: SELECT  test_debugger_explain();
        Result  (cost=0.00..0.26 rows=1 width=4) (actual time=6.596..6.597 rows=1 loops=1)

有了以上的用法，就可以在圖形化連線工具中正常使用用普通帳號使用 auto_explain 了～

以下為一個範例，在 pgAdmin4 v5.2 上的測試（黑色模式喔～）

PostgreSQL auto_explain 範例

參考資料

相關文章

為何 PostgreSQL 是 Mastercard 達到零停機的關鍵 ？

xDB 的過濾功能防止數據離開原本存放的資料庫。PostgreSQL 的觸發功能，使數據寫入目標資料庫時，將其他業務邏輯無縫應用於數據上。xDB 作為遷移即時資料的工具，這樣的資料遷移策略也被證實非常成功。

11 5 月 2022
從 Oracle 到 Postgres: 最完整的轉移指引五步驟 2/4篇:步驟一 －資料庫評估

Oracle 到 Postgres 的遷移被劃分為 5 個步驟或階段：資料庫評估、Schema 轉移、功能測試、效能測試和資料遷移。本文將對 Oracle 至 Postgres 前置作業的評估進行詳細的解說。

23 11 月 2022
Postgres 作為 Oracle 的最佳取代方案與遷移指南

比起 Oracle，Postgres 提供了更有彈性、更穩固和更合乎經濟效益的選擇。擁有像 EPAS 這種企業級的 Oracle 替代方案，任何規模企業的安全性和穩定性都能夠被滿足。在未來關聯式資料庫的領域中，O 這個單字將會有不同的意涵－開源（Open）。

28 10 月 2022
PGV21_image
Postgres Vision 2021：Day 1 Highlights

最精彩的 Postgres V…

29 6 月 2021
89 EDB & PGS 叢集架構&工具介紹
《歐立威科技 2023 研討會》8/9 | Oracle 移轉的下一站！EDB/Postgres 產業案例、叢集架構與常用工具介紹

EDB 亞太區 CTO Ajit Gadge 分享 Oracle 移轉到 EDB/Postgres 的常見問題以及電信／金融／製造業的成功移轉案例；技術顧問 Ken 也將分享 EDB 的叢集架構以及常用工具介紹，讓你在 Oracle 移轉之路暢行無阻！

02 8 月 2023
EDB-Webinar-cover-photo
2021.09.15《歐立威科技 2021 系列研討會》9/15 | 雲原生微服務，EDB postgres 容器化解決方案初探

本次研討會將介紹 EDB 打造的 postgres 資料庫 Kubernetes Operator 佈署方案 — Cloud Native PostgreSQL Operator，體驗在 Kubernetes 上佈建並管理 Postgres 資料庫，用於管理運行在私有、公共或混合雲環境中的 Kubernetes 上的 PostgreSQL 工作負載。

26 8 月 2021
EDB PostgreSQL
2018.09.28 EnterpriseDB Postgres 體驗營

EDB Postgres 進階版伺服器可提供社群版 PostgreSQL 所沒有的企業級功能。EnterpriseDB 提供成熟的 PostgreSQL 全面性解決方案，一體性規劃 PostgreSQL 的備份、高可用、管理架構，無須額外計費，替企業節省成本。

01 10 月 2018
EDB Postgres Advanced Server 11 (EPAS 11) 新功能

PostgreSQL 11 新功能整理如下：大幅強化 Partitioned Table 語法：跨分割區異動資料，新增預設分割區（Default Partition），增加 Hash Partition 模式，簡化索引、Primary Key、Unique Key 的建立

11 6 月 2019
KT 使用 PostgreSQL 和 EDB 提升 iPhone 訂單處理效率

考慮到整合性、安全性、管理效率等， KT 捨棄 Oracle、MySQL等方案，最後選擇與 Oracle 相容的 Postgres Plus Advanced Server來處理預購訂單 。系統上線後，在頭60秒成功收到30,000份 iPhone5 的訂單，系統流量擴大、效能提升、成本也下降了超過90%。

01 12 月 2020
美國電信巨頭將 100TB 資料從 Oracle 遷移至開源的 EDB Postgres

EDB 透過 EDB Postgres™ 平台強化 PostgreSQL 開源資料庫的性能、可調能力（scalability）、管理性、安全性和兼容性。透過 EDB Postgres 平台，客戶能擁有企業級的關鍵能力管理多模型資料（multi-model data），並能在多樣環境中快速部署。

27 5 月 2022
1004-1129-ETL-系列研討會
10/4 – 11/29 | ETL 系列研討會 – 資料庫與數據分析的前哨站！從資料工程到 PostgreSQL，從 ETL 工具到實戰解說！(共四場)

力邀資料科學家的工作日常粉專創辦人、PostgreSQL Taiwan 召集人、Pentaho 原廠講師及 ETL 資深技術專家，四位講師 x 四場講座，帶你從不同角度剖析 ETL；從資料工程到 PostgreSQL，從 ETL 工具到實戰解說！報名活動再抽酷炫個人化音響！

12 9 月 2023
EDB 協助 NTT DATA ABIC 提供日本金融機構低價、高可用性的雲端服務

EPAS 不僅以與 Oracle 資料庫有高兼容度聞名，還在降低優化批次程式成本方面為人所知。由於選用典型批次程序和驗證 Oracle 資料庫與 EDB 之間的兼容性，我們發現超過一半的程式不需優化即可遷移至 EDB。

16 5 月 2022
EDB Postgres Advanced Server 10 新功能

EDB Postgres Advanced Server 10 (EPAS 10)，基於 PostgreSQL 10發生了版本大跳躍，標誌著 PostgreSQL 的重大進展。
EPAS 10引進兩個令人振奮的功能 — 原生 Partitioned Table 語法，以及內建的高效表格層級資料同步，讓 PostgreSQL 的橫向擴充能力更上一層樓。

22 2 月 2018
EDB Webinar
2021.07.21 《歐立威科技 2021 系列研討會》7月｜EDB – Postgres Vision 2021 精華分享

Postgres Vision 2021為全球 Postgres 技術線上會議，本場研討會由資深顧問精挑細選最核心的精華內容，整理成1.5 小時的滿滿乾貨，讓您快速掌握 Postgres 的最新技術與未來趨勢！

22 6 月 2021
0515 EDB/Postgres Barman
《歐立威科技 2024 研討會》5/15 | Postgres 最新備份工具 – Barman 功能介紹及應用

本場活動將介紹 BART、Barman 各自的功能，以及使用的比較差異，並著重演示 Barman 語法介紹、參數設定，基礎備份還原操作模擬，以及實作中可能的雷和注意事項。

24 4 月 2024