PostgreSQL特性矩阵解析系列30之XML,JSON and YAML output for EXPLAIN

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
ANALYZE [ boolean ] -- 执行statement, 得到真实的运行时间以及统计信息
VERBOSE [ boolean ] -- 输出详细信息
COSTS [ boolean ] -- 输出cost值, 默认打开
BUFFERS [ boolean ] -- 输出本次QUERY shared 或 local buffer的信息. 命中,未命中,脏, 写 
TIMING [ boolean ] -- 输出时间开销
FORMAT { TEXT | XML | JSON | YAML } -- 输出格式


1.TEXT
postgres=# explain (analyze, verbose, costs, buffers, timing, format text) select 2 union select 2;
                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Unique  (cost=0.05..0.06 rows=2 width=4) (actual time=0.032..0.041 rows=1 loops=1)
   Output: (2)
   Buffers: shared hit=3
   ->  Sort  (cost=0.05..0.06 rows=2 width=4) (actual time=0.030..0.035 rows=2 loops=1)
         Output: (2)
         Sort Key: (2)
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=3
         ->  Append  (cost=0.00..0.04 rows=2 width=4) (actual time=0.002..0.008 rows=2 loops=1)
               ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.002 rows=1 loops=1)
                     Output: 2
               ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.000..0.001 rows=1 loops=1)
                     Output: 2
 Planning time: 0.110 ms
 Execution time: 0.106 ms
(15 rows)


2.XML
postgres=# explain (analyze, verbose, costs, buffers, timing, format XML) select 2 union select 2;
                             QUERY PLAN                              
---------------------------------------------------------------------
 <explain xmlns="http://www.postgresql.org/2009/explain">           +
   <Query>                                                          +
     <Plan>                                                         +
       <Node-Type>Unique</Node-Type>                                +
       <Parallel-Aware>false</Parallel-Aware>                       +
       <Startup-Cost>0.05</Startup-Cost>                            +
       <Total-Cost>0.06</Total-Cost>                                +
       <Plan-Rows>2</Plan-Rows>                                     +
       <Plan-Width>4</Plan-Width>                                   +
       <Actual-Startup-Time>0.015</Actual-Startup-Time>             +
       <Actual-Total-Time>0.022</Actual-Total-Time>                 +
       <Actual-Rows>1</Actual-Rows>                                 +
       <Actual-Loops>1</Actual-Loops>                               +
       <Output>                                                     +
         <Item>(2)</Item>                                           +
       </Output>                                                    +
       <Shared-Hit-Blocks>0</Shared-Hit-Blocks>                     +
       <Shared-Read-Blocks>0</Shared-Read-Blocks>                   +
       <Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>             +
       <Shared-Written-Blocks>0</Shared-Written-Blocks>             +
       <Local-Hit-Blocks>0</Local-Hit-Blocks>                       +
       <Local-Read-Blocks>0</Local-Read-Blocks>                     +
       <Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>               +
       <Local-Written-Blocks>0</Local-Written-Blocks>               +
       <Temp-Read-Blocks>0</Temp-Read-Blocks>                       +
       <Temp-Written-Blocks>0</Temp-Written-Blocks>                 +
       <Plans>                                                      +
         <Plan>                                                     +
           <Node-Type>Sort</Node-Type>                              +
           <Parent-Relationship>Outer</Parent-Relationship>         +
           <Parallel-Aware>false</Parallel-Aware>                   +
           <Startup-Cost>0.05</Startup-Cost>                        +
           <Total-Cost>0.06</Total-Cost>                            +
           <Plan-Rows>2</Plan-Rows>                                 +
           <Plan-Width>4</Plan-Width>                               +
           <Actual-Startup-Time>0.013</Actual-Startup-Time>         +
           <Actual-Total-Time>0.018</Actual-Total-Time>             +
           <Actual-Rows>2</Actual-Rows>                             +
           <Actual-Loops>1</Actual-Loops>                           +
           <Output>                                                 +
             <Item>(2)</Item>                                       +
           </Output>                                                +
           <Sort-Key>                                               +
             <Item>(2)</Item>                                       +
           </Sort-Key>                                              +
           <Sort-Method>quicksort</Sort-Method>                     +
           <Sort-Space-Used>25</Sort-Space-Used>                    +
           <Sort-Space-Type>Memory</Sort-Space-Type>                +
           <Shared-Hit-Blocks>0</Shared-Hit-Blocks>                 +
           <Shared-Read-Blocks>0</Shared-Read-Blocks>               +
           <Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>         +
           <Shared-Written-Blocks>0</Shared-Written-Blocks>         +
           <Local-Hit-Blocks>0</Local-Hit-Blocks>                   +
           <Local-Read-Blocks>0</Local-Read-Blocks>                 +
           <Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>           +
           <Local-Written-Blocks>0</Local-Written-Blocks>           +
           <Temp-Read-Blocks>0</Temp-Read-Blocks>                   +
           <Temp-Written-Blocks>0</Temp-Written-Blocks>             +
           <Plans>                                                  +
             <Plan>                                                 +
               <Node-Type>Append</Node-Type>                        +
               <Parent-Relationship>Outer</Parent-Relationship>     +
               <Parallel-Aware>false</Parallel-Aware>               +
               <Startup-Cost>0.00</Startup-Cost>                    +
               <Total-Cost>0.04</Total-Cost>                        +
               <Plan-Rows>2</Plan-Rows>                             +
               <Plan-Width>4</Plan-Width>                           +
               <Actual-Startup-Time>0.002</Actual-Startup-Time>     +
               <Actual-Total-Time>0.008</Actual-Total-Time>         +
               <Actual-Rows>2</Actual-Rows>                         +
               <Actual-Loops>1</Actual-Loops>                       +
               <Shared-Hit-Blocks>0</Shared-Hit-Blocks>             +
               <Shared-Read-Blocks>0</Shared-Read-Blocks>           +
               <Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks>     +
               <Shared-Written-Blocks>0</Shared-Written-Blocks>     +
               <Local-Hit-Blocks>0</Local-Hit-Blocks>               +
               <Local-Read-Blocks>0</Local-Read-Blocks>             +
               <Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>       +
               <Local-Written-Blocks>0</Local-Written-Blocks>       +
               <Temp-Read-Blocks>0</Temp-Read-Blocks>               +
               <Temp-Written-Blocks>0</Temp-Written-Blocks>         +
               <Plans>                                              +
                 <Plan>                                             +
                   <Node-Type>Result</Node-Type>                    +
                   <Parent-Relationship>Member</Parent-Relationship>+
                   <Parallel-Aware>false</Parallel-Aware>           +
                   <Startup-Cost>0.00</Startup-Cost>                +
                   <Total-Cost>0.01</Total-Cost>                    +
                   <Plan-Rows>1</Plan-Rows>                         +
                   <Plan-Width>4</Plan-Width>                       +
                   <Actual-Startup-Time>0.001</Actual-Startup-Time> +
                   <Actual-Total-Time>0.002</Actual-Total-Time>     +
                   <Actual-Rows>1</Actual-Rows>                     +
                   <Actual-Loops>1</Actual-Loops>                   +
                   <Output>                                         +
                     <Item>2</Item>                                 +
                   </Output>                                        +
                   <Shared-Hit-Blocks>0</Shared-Hit-Blocks>         +
                   <Shared-Read-Blocks>0</Shared-Read-Blocks>       +
                   <Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks> +
                   <Shared-Written-Blocks>0</Shared-Written-Blocks> +
                   <Local-Hit-Blocks>0</Local-Hit-Blocks>           +
                   <Local-Read-Blocks>0</Local-Read-Blocks>         +
                   <Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>   +
                   <Local-Written-Blocks>0</Local-Written-Blocks>   +
                   <Temp-Read-Blocks>0</Temp-Read-Blocks>           +
                   <Temp-Written-Blocks>0</Temp-Written-Blocks>     +
                 </Plan>                                            +
                 <Plan>                                             +
                   <Node-Type>Result</Node-Type>                    +
                   <Parent-Relationship>Member</Parent-Relationship>+
                   <Parallel-Aware>false</Parallel-Aware>           +
                   <Startup-Cost>0.00</Startup-Cost>                +
                   <Total-Cost>0.01</Total-Cost>                    +
                   <Plan-Rows>1</Plan-Rows>                         +
                   <Plan-Width>4</Plan-Width>                       +
                   <Actual-Startup-Time>0.000</Actual-Startup-Time> +
                   <Actual-Total-Time>0.001</Actual-Total-Time>     +
                   <Actual-Rows>1</Actual-Rows>                     +
                   <Actual-Loops>1</Actual-Loops>                   +
                   <Output>                                         +
                     <Item>2</Item>                                 +
                   </Output>                                        +
                   <Shared-Hit-Blocks>0</Shared-Hit-Blocks>         +
                   <Shared-Read-Blocks>0</Shared-Read-Blocks>       +
                   <Shared-Dirtied-Blocks>0</Shared-Dirtied-Blocks> +
                   <Shared-Written-Blocks>0</Shared-Written-Blocks> +
                   <Local-Hit-Blocks>0</Local-Hit-Blocks>           +
                   <Local-Read-Blocks>0</Local-Read-Blocks>         +
                   <Local-Dirtied-Blocks>0</Local-Dirtied-Blocks>   +
                   <Local-Written-Blocks>0</Local-Written-Blocks>   +
                   <Temp-Read-Blocks>0</Temp-Read-Blocks>           +
                   <Temp-Written-Blocks>0</Temp-Written-Blocks>     +
                 </Plan>                                            +
               </Plans>                                             +
             </Plan>                                                +
           </Plans>                                                 +
         </Plan>                                                    +
       </Plans>                                                     +
     </Plan>                                                        +
     <Planning-Time>0.036</Planning-Time>                           +
     <Triggers>                                                     +
     </Triggers>                                                    +
     <Execution-Time>0.039</Execution-Time>                         +
   </Query>                                                         +
 </explain>
(1 row)


3.JSON
postgres=# explain (analyze, verbose, costs, buffers, timing, format JSON) select 2 union select 2;
                     QUERY PLAN                     
----------------------------------------------------
 [                                                 +
   {                                               +
     "Plan": {                                     +
       "Node Type": "Unique",                      +
       "Parallel Aware": false,                    +
       "Startup Cost": 0.05,                       +
       "Total Cost": 0.06,                         +
       "Plan Rows": 2,                             +
       "Plan Width": 4,                            +
       "Actual Startup Time": 0.015,               +
       "Actual Total Time": 0.023,                 +
       "Actual Rows": 1,                           +
       "Actual Loops": 1,                          +
       "Output": ["(2)"],                          +
       "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,                   +
       "Plans": [                                  +
         {                                         +
           "Node Type": "Sort",                    +
           "Parent Relationship": "Outer",         +
           "Parallel Aware": false,                +
           "Startup Cost": 0.05,                   +
           "Total Cost": 0.06,                     +
           "Plan Rows": 2,                         +
           "Plan Width": 4,                        +
           "Actual Startup Time": 0.014,           +
           "Actual Total Time": 0.018,             +
           "Actual Rows": 2,                       +
           "Actual Loops": 1,                      +
           "Output": ["(2)"],                      +
           "Sort Key": ["(2)"],                    +
           "Sort Method": "quicksort",             +
           "Sort Space Used": 25,                  +
           "Sort Space Type": "Memory",            +
           "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,               +
           "Plans": [                              +
             {                                     +
               "Node Type": "Append",              +
               "Parent Relationship": "Outer",     +
               "Parallel Aware": false,            +
               "Startup Cost": 0.00,               +
               "Total Cost": 0.04,                 +
               "Plan Rows": 2,                     +
               "Plan Width": 4,                    +
               "Actual Startup Time": 0.002,       +
               "Actual Total Time": 0.009,         +
               "Actual Rows": 2,                   +
               "Actual Loops": 1,                  +
               "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,           +
               "Plans": [                          +
                 {                                 +
                   "Node Type": "Result",          +
                   "Parent Relationship": "Member",+
                   "Parallel Aware": false,        +
                   "Startup Cost": 0.00,           +
                   "Total Cost": 0.01,             +
                   "Plan Rows": 1,                 +
                   "Plan Width": 4,                +
                   "Actual Startup Time": 0.001,   +
                   "Actual Total Time": 0.002,     +
                   "Actual Rows": 1,               +
                   "Actual Loops": 1,              +
                   "Output": ["2"],                +
                   "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        +
                 },                                +
                 {                                 +
                   "Node Type": "Result",          +
                   "Parent Relationship": "Member",+
                   "Parallel Aware": false,        +
                   "Startup Cost": 0.00,           +
                   "Total Cost": 0.01,             +
                   "Plan Rows": 1,                 +
                   "Plan Width": 4,                +
                   "Actual Startup Time": 0.000,   +
                   "Actual Total Time": 0.001,     +
                   "Actual Rows": 1,               +
                   "Actual Loops": 1,              +
                   "Output": ["2"],                +
                   "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        +
                 }                                 +
               ]                                   +
             }                                     +
           ]                                       +
         }                                         +
       ]                                           +
     },                                            +
     "Planning Time": 0.038,                       +
     "Triggers": [                                 +
     ],                                            +
     "Execution Time": 0.042                       +
   }                                               +
 ]
(1 row)


4.YAML
postgres=# explain (analyze, verbose, costs, buffers, timing, format YAML) select 2 union select 2;
                  QUERY PLAN                   
-----------------------------------------------
 - Plan:                                      +
     Node Type: "Unique"                      +
     Parallel Aware: false                    +
     Startup Cost: 0.05                       +
     Total Cost: 0.06                         +
     Plan Rows: 2                             +
     Plan Width: 4                            +
     Actual Startup Time: 0.016               +
     Actual Total Time: 0.024                 +
     Actual Rows: 1                           +
     Actual Loops: 1                          +
     Output:                                  +
       - "(2)"                                +
     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                   +
     Plans:                                   +
       - Node Type: "Sort"                    +
         Parent Relationship: "Outer"         +
         Parallel Aware: false                +
         Startup Cost: 0.05                   +
         Total Cost: 0.06                     +
         Plan Rows: 2                         +
         Plan Width: 4                        +
         Actual Startup Time: 0.015           +
         Actual Total Time: 0.019             +
         Actual Rows: 2                       +
         Actual Loops: 1                      +
         Output:                              +
           - "(2)"                            +
         Sort Key:                            +
           - "(2)"                            +
         Sort Method: "quicksort"             +
         Sort Space Used: 25                  +
         Sort Space Type: "Memory"            +
         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               +
         Plans:                               +
           - Node Type: "Append"              +
             Parent Relationship: "Outer"     +
             Parallel Aware: false            +
             Startup Cost: 0.00               +
             Total Cost: 0.04                 +
             Plan Rows: 2                     +
             Plan Width: 4                    +
             Actual Startup Time: 0.002       +
             Actual Total Time: 0.009         +
             Actual Rows: 2                   +
             Actual Loops: 1                  +
             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           +
             Plans:                           +
               - Node Type: "Result"          +
                 Parent Relationship: "Member"+
                 Parallel Aware: false        +
                 Startup Cost: 0.00           +
                 Total Cost: 0.01             +
                 Plan Rows: 1                 +
                 Plan Width: 4                +
                 Actual Startup Time: 0.001   +
                 Actual Total Time: 0.002     +
                 Actual Rows: 1               +
                 Actual Loops: 1              +
                 Output:                      +
                   - "2"                      +
                 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       +
               - Node Type: "Result"          +
                 Parent Relationship: "Member"+
                 Parallel Aware: false        +
                 Startup Cost: 0.00           +
                 Total Cost: 0.01             +
                 Plan Rows: 1                 +
                 Plan Width: 4                +
                 Actual Startup Time: 0.000   +
                 Actual Total Time: 0.001     +
                 Actual Rows: 1               +
                 Actual Loops: 1              +
                 Output:                      +
                   - "2"                      +
                 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       +
   Planning Time: 0.038                       +
   Triggers:                                  +
   Execution Time: 0.043
(1 row)
复制