作者：太陽

一、查詢當前正在運行的Top SQL

查詢當前正在運行的會話中耗時最長的Top SQL，where條件可按需修改

SELECT pgsa.datname AS database_name, pgsa.usename AS user_name, pgsa.client_addr AS client_addr, pgsa.application_name AS application_name, pgsa.state AS state, pgsa.backend_start AS backend_start, pgsa.xact_start AS xact_start, extract(epoch FROM now() - pgsa.xact_start) AS xact_time, pgsa.query_start AS query_start, extract(epoch FROM now() - pgsa.query_start) AS query_time, pgsa.query AS query_sql
FROM pg_stat_activity pgsa
WHERE pgsa.state != 'idle'AND pgsa.state != 'idle in transaction'AND pgsa.state != 'idle in transaction (aborted)'
ORDER BY query_time DESC
LIMIT 20;

pg_stat_activity視圖各字段含義：http://postgres.cn/docs/13/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW

二、查詢某個時間點之后的Top SQL

1、pg_stat_statements介紹

pg_stat_statements可跟蹤服務器執行的所有SQL語句的計劃信息和執行統計信息，詳見官方文檔說明：[http://postgres.cn/docs/13/pgstatstatements.html](http://postgres.cn/docs/13/pgstatstatements.html)

2、安裝配置

1)修改配置文件

# su - postgres
$ vi /data/pgsql13/data/postgresql.conf
shared_preload_libraries='pg_stat_statements,pg_pathman'        
pg_stat_statements.max = 10000
pg_stat_statements.track = all
2)重啟生效：$ pg_ctl -D /data/pgsql13/data restart

3)載入pg_stat_statement插件

postgres=# \x
Expanded display is on.--查看可用模塊
postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
-[ RECORD 1 ]-----+-----------------------------------------------------------------------
name              | pg_stat_statements
default_version   | 1.8
installed_version | 
comment           | track planning and execution statistics of all SQL statements executed--載入模塊，載入后pg_stat_statements表可正常使用
postgres=# create extension pg_stat_statements; 
CREATE EXTENSION

3、pg_stat_statements各字段含義

postgres=# select * from  pg_stat_statements limit 1;
-[ RECORD 1 ]-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
userid              | 16480				//用戶oid
dbid                | 163959				//數據庫oid
queryid             | -7584655433466348220	        //查詢id
query               | SELECT ......			//SQL語句
plans               | 0 				//計劃語句的次數，單位ms(啟用pg_stat_statements.track_planning才會統計，默認關閉)
total_plan_time     | 0 				//計劃語句所花費的總時間，單位ms(啟用pg_stat_statements.track_planning才會統計，默認關閉)
min_plan_time       | 0 				//計劃語句所花費的最短時間，單位ms(啟用pg_stat_statements.track_planning才會統計，默認關閉)
max_plan_time       | 0 				//計劃語句所花費的最長時間，單位ms(啟用pg_stat_statements.track_planning才會統計，默認關閉)
mean_plan_time      | 0 				//計劃語句所花費的平均時間，單位ms(啟用pg_stat_statements.track_planning才會統計，默認關閉)
stddev_plan_time    | 0 				//計劃語句花費的時間的總體標準偏差，單位ms(啟用pg_stat_statements.track_planning才會統計，默認關閉)
calls               | 92545				//語句被執行的次數
total_exec_time     | 1563.9699899999862		//執行語句所花費的總時間，單位ms
min_exec_time       | 0.005605			        //執行語句所花費的最短時間，
max_exec_time       | 7.055763			        //執行語句所花費的最長時間，
mean_exec_time      | 0.01689956226700567		//執行語句所花費的最長時間，
stddev_exec_time    | 0.036137014177393116	        //執行語句花費的時間的總體標準偏差，
rows                | 17277				//語句檢索或影響的總行數
shared_blks_hit     | 394706				//語句的共享塊緩存命中總數
shared_blks_read    | 7 				//語句讀取的共享塊總數
shared_blks_dirtied | 6 				//被語句弄臟的共享塊總數
shared_blks_written | 0 				//語句寫入的共享塊總數
local_blks_hit      | 0 				//語句的本地塊緩存命中總數
local_blks_read     | 0 				//語句讀取的本地塊總數
local_blks_dirtied  | 0 				//被語句弄臟的本地塊總數
local_blks_written  | 0 				//語句寫入的本地塊總數
temp_blks_read      | 0 				//語句讀取的臨時塊總數
temp_blks_written   | 0 				//語句寫入的臨時塊總數
blk_read_time       | 0 				//語句讀取塊所花費的總時間
blk_write_time      | 0 				//語句寫入塊所花費的總時間
wal_records         | 7874 				//語句生成的 WAL 記錄總數
wal_fpi             | 5 				//語句生成的 WAL 整頁圖像總數
wal_bytes           | 450177 				//語句生成的 WAL 字節總數

oid是唯一標識，查詢用戶名與用戶oid的關系：

postgres=# select userid,userid::regrole from pg_stat_statements group by userid;
-[ RECORD 1 ]----
userid | 10
userid | postgres
...

查詢Top SQL：

-- 按總執行時間查詢Top SQL
select userid::regrole as user_name,* from pg_stat_statements order by total_exec_time desc limit 20;
-- 按總IO消耗查詢Top SQL
select userid::regrole as user_name,* from pg_stat_statements order by blk_read_time+blk_write_time desc limit 20;
-- 按總調用次數查詢Top SQL
select userid::regrole as user_name,* from pg_stat_statements order by calls desc limit 20;

4、pg_stat_statements的限制

pg_stat_statements是累積的統計，累積的是pg_stat_statements配置后至當前時刻，無法查詢這期間指定時間范圍內的Top SQL情況；
可通過如下命令，清理歷史統計信息：

select pg_stat_statements_reset();

5、通過pg_stat_statements實現查詢指定時間范圍內的Top SQL

通過定時清理歷史統計信息+定時查詢pg_stat_statements的方式可實現查詢指定時間范圍內的Top SQL；

如在每天0點清理歷史統計信息，在每天9、11、17點分別查詢pg_stat_statements，可得到每天0～9、0～11、0～17這3個時間范圍內的Top SQL。

更多技術信息請查看云掣官網https://yunche.pro/?t=yrgw