PostgreSQL 15 preview - explain buffers 增加支持临时文件IO timing跟踪

原创 digoal 2022-01-20

368

作者

digoal

日期

2022-04-08

Track I/O timing for temporary file blocks in EXPLAIN (BUFFERS) master github/master  
author  Michael Paquier <michael@paquier.xyz>     
Fri, 8 Apr 2022 02:27:21 +0000 (11:27 +0900)  
committer   Michael Paquier <michael@paquier.xyz>     
Fri, 8 Apr 2022 02:27:21 +0000 (11:27 +0900)  
commit  efb0ef909f605817da6b77c1f3fef0a24457ec16  
tree    cc85a34ce3c1918a439f69e3e6f0bab1191cd486    tree  
parent  dafae9707ab7e7079ce1ba22cebda4557d0cbaf3    commit | diff  
Track I/O timing for temporary file blocks in EXPLAIN (BUFFERS)  
Previously, the output of EXPLAIN (BUFFERS) option showed only the I/O  
timing spent reading and writing shared and local buffers.  This commit  
adds on top of that the I/O timing for temporary buffers in the output  
of EXPLAIN (for spilled external sorts, hashes, materialization. etc).  
This can be helpful for users in cases where the I/O related to  
temporary buffers is the bottleneck.  
Like its cousin, this information is available only when track_io_timing  
is enabled.  Playing the patch, this is showing an extra overhead of up  
to 1% even when using gettimeofday() as implementation for interval  
timings, which is slightly within the usual range noise still that's  
measurable.  
Author: Masahiko Sawada  
Reviewed-by: Georgios Kokolatos, Melanie Plageman, Julien Rouhaud,  
Ranier Vilela  
Discussion: https://postgr.es/m/CAD21AoAJgotTeP83p6HiAGDhs_9Fw9pZ2J=_tYTsiO5Ob-V5GQ@mail.gmail.com

+-- Check output including I/O timings.  These fields are conditional  
+-- but always set in JSON format, so check them only in this case.  
+set track_io_timing = on;  
+select explain_filter('explain (analyze, buffers, format json) select * from int8_tbl i8');  
+           explain_filter             
+------------------------------------  
+ [                                 +  
+   {                               +  
+     "Plan": {                     +  
+       "Node Type": "Seq Scan",    +  
+       "Parallel Aware": false,    +  
+       "Async Capable": false,     +  
+       "Relation Name": "int8_tbl",+  
+       "Alias": "i8",              +  
+       "Startup Cost": N.N,        +  
+       "Total Cost": N.N,          +  
+       "Plan Rows": N,             +  
+       "Plan Width": N,            +  
+       "Actual Startup Time": N.N, +  
+       "Actual Total Time": N.N,   +  
+       "Actual Rows": N,           +  
+       "Actual Loops": N,          +  
+       "Shared Hit Blocks": N,     +  
+       "Shared Read Blocks": N,    +  
+       "Shared Dirtied Blocks": N, +  
+       "Shared Written Blocks": N, +  
+       "Local Hit Blocks": N,      +  
+       "Local Read Blocks": N,     +  
+       "Local Dirtied Blocks": N,  +  
+       "Local Written Blocks": N,  +  
+       "Temp Read Blocks": N,      +  
+       "Temp Written Blocks": N,   +  
+       "I/O Read Time": N.N,       +  
+       "I/O Write Time": N.N,      +  
+       "Temp I/O Read Time": N.N,  +  
+       "Temp I/O Write Time": N.N  +  
+     },                            +  
+     "Planning": {                 +  
+       "Shared Hit Blocks": N,     +  
+       "Shared Read Blocks": N,    +  
+       "Shared Dirtied Blocks": N, +  
+       "Shared Written Blocks": N, +  
+       "Local Hit Blocks": N,      +  
+       "Local Read Blocks": N,     +  
+       "Local Dirtied Blocks": N,  +  
+       "Local Written Blocks": N,  +  
+       "Temp Read Blocks": N,      +  
+       "Temp Written Blocks": N,   +  
+       "I/O Read Time": N.N,       +  
+       "I/O Write Time": N.N,      +  
+       "Temp I/O Read Time": N.N,  +  
+       "Temp I/O Write Time": N.N  +  
+     },                            +  
+     "Planning Time": N.N,         +  
+     "Triggers": [                 +  
+     ],                            +  
+     "Execution Time": N.N         +  
+   }                               +  
+ ]  
+(1 row)  
+  
+set track_io_timing = off;