Analysis of common parameters of execution plan

Keywords: xml SQL JSON PostgreSQL

Actually execute the actual SQL, and see the time of each step of the execution plan
Additional information for explicit planning, additional information: columns output by each node in the planning tree. The trigger being fired also displays the name of the initiator. The default is FALSE.
Start cost and total cost, number of lines, and width of each line of each plan node. Costs is TRUE by default
The information used by the buffer can only be used with the analyze parameter, sharing blocks, local blocks, temporary blocks (read and write). Shared block, local block and temporary block contain table and index, temporary table and temporary index respectively. Default false
Specifies that the output format (text\xml\json\yaml) defaults to text

highgo=# explain (analyze,buffers,format xml)select profession from test where id = 25;
                         QUERY PLAN                          
 <explain xmlns="">   +
   <Query>                                                  +
     <Plan>                                                 +
       <Node-Type>Seq Scan</Node-Type>                      +
       <Relation-Name>test</Relation-Name>                  +
       <Alias>test</Alias>                                  +
       <Startup-Cost>0.00</Startup-Cost>                    +
       <Total-Cost>19.56</Total-Cost>                       +
       <Plan-Rows>1</Plan-Rows>                             +
       <Plan-Width>12</Plan-Width>                          +
       <Actual-Startup-Time>0.007</Actual-Startup-Time>     +
       <Actual-Total-Time>0.096</Actual-Total-Time>         +
       <Actual-Rows>1</Actual-Rows>                         +
       <Actual-Loops>1</Actual-Loops>                       +
       <Filter>(id = 25)</Filter>                           +
       <Shared-Hit-Blocks>7</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>                                                +
     <Planning-Time>0.036</Planning-Time>                   +
     <Triggers>                                             +
     </Triggers>                                            +
     <Execution-Time>0.109</Execution-Time>                 +
   </Query>                                                 +
(1 row)

If you do not want to modify the data, you can put explain analyze into a transaction and roll back after execution.

highgo=# begin;
highgo=# explain analyze insert into test values (2,'Zhang San','qwer');
                                          QUERY PLAN                                          
 Insert on test  (cost=0.00..0.01 rows=1 width=0) (actual time=12.475..12.475 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1)
 Planning time: 0.132 ms
 Execution time: 116.692 ms
(4 rows)

highgo=# rollback;

BY sea is boundless

Posted by chard on Sun, 03 May 2020 14:52:16 -0700