PostgreSQL17 EXPLAIN（BUFFERS）新增local IO块读写时间统计信息

有一段时间没写文章了，咱们接着写PostgreSQL17的中关于EXPLAIN相关的新特性。这期我们来看看，在EXPLAIN的BUFFERS输出中添加了local IO块读/写时间统计信息。查看这个IO统计信息，我们还需要设置一个track_io_timing参数，具体的定义如下：

track_io_timing  (boolean)
启用对系统 I/O 调用的计时。这个参数默认为关闭，因为它将重复地向操作系统查询当前时间，这会在某些平台上导致显著的负荷。 你可以使用pg_test_timing工具来度量你的系统中计时的开销。 I/O 计时信息被显示在 pg_stat_database中， 当BUFFERS选项被使用时的EXPLAIN输出中，通过autovacuum对auto-vacuums 和 auto-analyzes，当 log_autovacuum_min_duration 被pg_stat_statements设置时。 只有超级用户可以更改这个设置。 

在这里我们先来看看PG16中的执行计划

set track_io_timing = on;
testdb=# explain (analyze,buffers, format json) select * from rank1;
             QUERY PLAN
-------------------------------------
 [                                  +
   {                                +
     "Plan": {                      +
       "Node Type": "Seq Scan",     +
       "Parallel Aware": false,     +
       "Async Capable": false,      +
       "Relation Name": "rank1",    +
       "Alias": "rank1",            +
       "Startup Cost": 0.00,        +
       "Total Cost": 20310.00,      +
       "Plan Rows": 1000000,        +
       "Plan Width": 49,            +
       "Actual Startup Time": 0.054,+
       "Actual Total Time": 76.261, +
       "Actual Rows": 1000000,      +
       "Actual Loops": 1,           +
       "Shared Hit Blocks": 64,     +
       "Shared Read Blocks": 10246, +
       "Shared Dirtied Blocks": 0,  +
       "Shared Written Blocks": 0,  +
       "Local Hit Blocks": 0,       +
       "Local Read Blocks": 0,      +
       "Local Dirtied Blocks": 0,   +
       "Local Written Blocks": 0,   +
       "Temp Read Blocks": 0,       +
       "Temp Written Blocks": 0,    +
       "I/O Read Time": 25.896,     +
       "I/O Write Time": 0.000,     +
       "Temp I/O Read Time": 0.000, +
       "Temp I/O Write Time": 0.000 +
     },                             +
     "Planning": {                  +
       "Shared Hit Blocks": 0,      +
       "Shared Read Blocks": 0,     +
       "Shared Dirtied Blocks": 0,  +
       "Shared Written Blocks": 0,  +
       "Local Hit Blocks": 0,       +
       "Local Read Blocks": 0,      +
       "Local Dirtied Blocks": 0,   +
       "Local Written Blocks": 0,   +
       "Temp Read Blocks": 0,       +
       "Temp Written Blocks": 0,    +
       "I/O Read Time": 0.000,      +
       "I/O Write Time": 0.000,     +
       "Temp I/O Read Time": 0.000, +
       "Temp I/O Write Time": 0.000 +
     },                             +
     "Planning Time": 0.033,        +
     "Triggers": [                  +
     ],                             +
     "Execution Time": 101.745      +
   }                                +
 ]
(1 row)

通过上面的执行计划，可以看出在PG16中，IO相关的统计信息有I/O Write/Read和Temp I/O Write/Read信息。另外我也通过查看官方的邮件了解到，在PG17之前的版本中，其实I/O Write/Read只包含share共享块的IO信息，没有包含local块的信息。下面我们来看看PG17中的执行计划。

set track_io_timing = on;
testdb=# explain (analyze,buffers, format json) select * from rank1;
               QUERY PLAN
----------------------------------------
 [                                     +
   {                                   +
     "Plan": {                         +
       "Node Type": "Seq Scan",        +
       "Parallel Aware": false,        +
       "Async Capable": false,         +
       "Relation Name": "rank1",       +
       "Alias": "rank1",               +
       "Startup Cost": 0.00,           +
       "Total Cost": 20310.00,         +
       "Plan Rows": 1000000,           +
       "Plan Width": 49,               +
       "Actual Startup Time": 0.826,   +
       "Actual Total Time": 469.417,   +
       "Actual Rows": 1000000,         +
       "Actual Loops": 1,              +
       "Shared Hit Blocks": 0,         +
       "Shared Read Blocks": 10310,    +
       "Shared Dirtied Blocks": 0,     +
       "Shared Written Blocks": 0,     +
       "Local Hit Blocks": 0,          +
       "Local Read Blocks": 0,         +
       "Local Dirtied Blocks": 0,      +
       "Local Written Blocks": 0,      +
       "Temp Read Blocks": 0,          +
       "Temp Written Blocks": 0,       +
       "Shared I/O Read Time": 408.628,+
       "Shared I/O Write Time": 0.000, +
       "Local I/O Read Time": 0.000,   +
       "Local I/O Write Time": 0.000,  +
       "Temp I/O Read Time": 0.000,    +
       "Temp I/O Write Time": 0.000    +
     },                                +
     "Planning": {                     +
       "Shared Hit Blocks": 29,        +
       "Shared Read Blocks": 4,        +
       "Shared Dirtied Blocks": 1,     +
       "Shared Written Blocks": 0,     +
       "Local Hit Blocks": 0,          +
       "Local Read Blocks": 0,         +
       "Local Dirtied Blocks": 0,      +
       "Local Written Blocks": 0,      +
       "Temp Read Blocks": 0,          +
       "Temp Written Blocks": 0,       +
       "Shared I/O Read Time": 15.202, +
       "Shared I/O Write Time": 0.000, +
       "Local I/O Read Time": 0.000,   +
       "Local I/O Write Time": 0.000,  +
       "Temp I/O Read Time": 0.000,    +
       "Temp I/O Write Time": 0.000    +
     },                                +
     "Planning Time": 15.425,          +
     "Triggers": [                     +
     ],                                +
     "Execution Time": 500.778         +
   }                                   +
 ]
(1 row)

在上面的执行计划中，我们可以看到EXPLAIN的输出信息中IO相关的信息有了变化，新增了Shared I/O Read Time、Shared I/O Write Time、Local I/O Read Time和Local I/O Write Time，其实在这里Shared I/O Read/Write 就是之前的I/O Write/Read，而Local I/O Write/Read才是本次新增的内容，用于描述local块I/O的读写时间统计数据。这也就是官网在描述的时候，只说了新增的是Local I/O Write/Read，而不是新增Shared 和local块的Write/Read。

总结

其实也没啥总结的内容，这个功能只是在explain的输出内容中多了一些读写local块的IO时间信息，对于我们定位一些问题可能会有一定的参考意义。

参考

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=295c36c0c
– / END / –

可以通过下面的方式联系我

• 微信公众号：@墨竹札记
• 墨天轮：@墨竹
• 微信：wshf395062788
• PGFans：@墨竹

如果这篇文章为你带来了灵感或启发，就请帮忙点赞、收藏、转发；如果文章中不严谨或者错漏之处，请及时评论指正。非常感谢！