temp_blks_written:临时缓冲区写入的块数。 blk_read_time:块读取总时间(毫秒)。 blk_write_time:块写入总时间(毫秒)。 使用pg_stat_statements,可以执行各种分析来优化查询性能。例如: 查找执行时间最长的查询: SELECTquery,total_time,calls,mean_timeFROMpg_stat_statementsORDERBYtotal_timeDESCLIMIT10; 查找执...
total_time:SQL语句的总执行时间。 rows:SQL语句返回的总行数。 shared_blks_hit:SQL语句从共享缓冲区中读取的块数。 shared_blks_read:SQL语句从磁盘读取的块数。 shared_blks_dirtied:SQL语句在共享缓冲区中修改的块数。 shared_blks_written:SQL语句写入共享缓冲区的块数。
pg_stat_statements_reset---(1row)-- Monitoring using pg_stat_statements:SELECTsubstring(query,1,50)ASquery, round(total_time::numeric,2)AStotal_time, calls, round(mean_time::numeric,2)ASmean, round((100*total_time/sum(total_time::numeric)OVER())::numeric,2)ASpercentage_cpuFROMpg_stat...
Allow pg_stat_statements to optionally track the planning time of statements (Julien Rouhaud, Pascal Legrand, Thomas Munro, Fujii Masao) Previously only execution time was tracked. pg_stat_statements新增了以下字段跟踪执行计划的时间信息,如下: plans: SQL被解析生成执行计划的次数 total_plan_time: 生成...
select * from pg_stat_statements order by total_time desc limit 10; #平均执行时间较长的SQL select * from pg_stat_statements order by mean_time desc limit 10; #在读/写块上总执行时间最多的SQL select * from pg_stat_statements order by (blk_read_time+blk_write_time) desc limit 10; ...
bench=# SELECT pg_stat_statements_reset(); $ pgbench -i bench $ pgbench -c10 -t300 bench bench=# \x bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time...
query | alter table pgbench_accounts add primary key (aid) calls | 1 total_time | 81.42 rows | 0 hit_percent | 34.4947735191637631 F.30.5. 作者 Takahiro Itagaki<itagaki.takahiro@oss.ntt.co.jp>。Peter Geoghegan<peter@2ndquadrant.com>
bench=# SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5; -[ RECORD 1 ]--- query | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE...
total_time double precision 执行总时间 (平均值=total_time/calls ) rows bigint 影响的总行数 shared_blks_hit bigint 共享块命中数量 shared_blks_read bigint 共享块读数量 shared_blks_written bigint 共享块写数量 local_blks_hit bigint 本地块命中数量 ...
(total_time/calls)asavg, +| | query +| |FROMpg_stat_statements +| |ORDERBY1DESC +| | LIMIT $3| |SELECT+|4.9146500000000005e-06|0.294879(total_time / $1/ $2)astotal, +| | (total_time/calls)asavg +| |FROMpg_stat_statements +| |ORDERBY1DESC +| | ...