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

husthxd發表於2020-04-10

在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.](https://www.depesz.com/2020/04/02/waiting-for-postgresql-13-allow-pg_stat_statements-to-track-planning-statistics/)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/6906/viewspace-2685525/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

PostgreSQL DBA(63) - Extension(pg_stat_statements)
2019-07-26
SQL
PostgreSQL DBA(138) - PG 13（Drop database force）
2019-12-04
SQLDatabase
PostgreSQL DBA(172) - PG 13（WAL activity in EXPLAIN）
2020-04-14
SQLAI
PostgreSQL DBA(174) - PG 13（Allow inserts to trigger autovacuum）
2020-05-22
SQL
PostgreSQL DBA(137) - PG 13（Allow invisible PROMPT2 in psql）
2019-12-03
SQL
PostgreSQL DBA(91) - PG upgrade
2019-09-12
SQL
PostgreSQL DBA(40) - PG 12 pg_promote
2019-06-20
SQL
PostgreSQL DBA(125) - PG 12（TPCC）
2019-11-11
SQL
PostgreSQL DBA(41) - PG Index Properties
2019-06-25
SQLIndex
PostgreSQL DBA(142) - PG 12（Monitoring PostgreSQL VACUUM processes）
2019-12-10
SQL
PostgreSQL DBA(82) - PG 12 Improving COPY
2019-08-20
SQL
PostgreSQL DBA(83) - Extension(pg_buffercache)
2019-08-21
SQL
PostgreSQL DBA(84) - Extension(pg_prewarm)
2019-08-21
SQL
PostgreSQL DBA(162) - Extension(pg_catcheck)
2020-02-11
SQL
PostgreSQL DBA(92) - PG 12 Improving VACUUM
2019-09-16
SQL
PostgreSQL DBA(67) - PG 12 SQLJSON path
2019-08-01
SQLJSON
PostgreSQL DBA(63) - Extension(pg_qualstats)
2019-07-26
SQL
PostgreSQL DBA(163) - Extension(pg_cron)
2020-02-11
SQL
PostgreSQL DBA(53) - PG 12 Generated columns
2019-07-12
SQL
PostgreSQL DBA(37) - PG 12 REINDEX CONCURRENTLY
2019-06-17
SQLIndex
PostgreSQL DBA(39) - PG 12 Functions for partitions
2019-06-19
SQLFunction
PostgreSQL DBA(46) - PG Operator classes and families
2019-07-01
SQL
PostgreSQL DBA(36) - PG 12 Inlined WITH queries
2019-06-04
SQLinline
PostgreSQL DBA(189) - PG 14 Monitoring Improvements
2022-03-21
SQL
PostgreSQL DBA(183) - PG 14(Better JSON)
2021-06-05
SQLJSON
PostgreSQL DBA(146) - pgAdmin（pg_dumpall vs pg_dump）
2019-12-16
SQL
PostgreSQL DBA(79) - Locks(pg_blocking_pids)
2019-08-15
SQLBloC
PostgreSQL DBA(94) - PG 12 Improving Partition(Select)
2019-09-20
SQL
PostgreSQL DBA(93) - PG 12 Improving Partition(Insert)
2019-09-19
SQL
PostgreSQL DBA(129) - Extension(pg_variables).md
2019-11-18
SQL
PostgreSQL DBA(62) - PG 12 More progress reporting
2019-07-25
SQL
PostgreSQL DBA(188) - PG 14 enable_memoize
2022-02-22
SQL
PostgreSQL DBA(18) - pg_waldump工具簡介
2018-12-20
SQL
PostgreSQL DBA(4) - PG 11 New Features#1
2018-09-19
SQL
PostgreSQL DBA(5) - PG 11 New Features#2
2018-09-20
SQL
PostgreSQL DBA(6) - PG 11 New Features#3
2018-09-21
SQL
PostgreSQL DBA(70) - PG 12 Add SETTINGS option to EXPLAIN
2019-08-07
SQLAI
PostgreSQL DBA(98) - PG 12 Faster float conversion to text
2019-09-26
SQLAST