ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(171) - PG 13(pg_stat_statements to track planning statistics)

PostgreSQL DBA(171) - PG 13(pg_stat_statements to track planning statistics)

原创 PostgreSQL 作者:husthxd 时间:2020-04-10 18:09:32 0 删除 编辑

在PG 13的pg_stat_statements中新增了计划相关的时间.

PG 12

[local:/data/run/pg12]:5120 pg12@testdb=# select * from pg_stat_statements where query like 'select count(*) from tmp%';
-[ RECORD 1 ]-------+-------------------------
userid              | 10
dbid                | 16384
queryid             | -8684501269442258252
query               | select count(*) from tmp
calls               | 1
total_time          | 199.761882
min_time            | 199.761882
max_time            | 199.761882
mean_time           | 199.761882
stddev_time         | 0
rows                | 1
shared_blks_hit     | 4425
shared_blks_read    | 0
shared_blks_dirtied | 4425
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

PG 12中只有总的时间和最大最小时间,但没有计划阶段的时间.

PG 13

[local:/data/run/pg13]:5130 pg13@masterdb=# select * from pg_stat_statements where query like 'select count(*) from tmp%';
-[ RECORD 1 ]-------+-------------------------
userid              | 10
dbid                | 16384
queryid             | 8012662698209620852
query               | select count(*) from tmp
plans               | 2
total_plan_time     | 1.6762450000000002
min_plan_time       | 0.255869
max_plan_time       | 1.420376
mean_plan_time      | 0.8381225000000001
stddev_plan_time    | 0.5822535000000001
calls               | 2
total_exec_time     | 156.125558
min_exec_time       | 71.703524
max_exec_time       | 84.42203400000001
mean_exec_time      | 78.062779
stddev_exec_time    | 6.3592550000000045
rows                | 2
shared_blks_hit     | 8875
shared_blks_read    | 1
shared_blks_dirtied | 0
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         | 0
wal_num_fpw         | 0
wal_bytes           | 0
[local:/data/run/pg13]:5130 pg13@masterdb=# 
[local:/data/run/pg13]:5130 pg13@masterdb=#

在PG 13中,新增了计划时间(X_plan_time).

参考资料
Waiting for PostgreSQL 13 – Allow pg_stat_statements to track planning statistics.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2685525/,如需转载,请注明出处,否则将追究法律责任。

全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。

注册时间:2007-12-28

  • 博文量
    1501
  • 访问量
    3963979