Product Documentation

Appearance

7.1.2
7.0.2
6.9.3
5.4.17
5.2.31

sys.log_query_explain ​

This view records the plan for each query and contains a row for each node in the plan. A unique row in this table is identified by a combination of plan ID and node ID. The rows in the table map to the node-level text output that is returned when you run an EXPLAIN ANALYZE command.

Column NameData TypeDescription
plan_idvarchar(64)Query plan ID. You can join this column to the plan_id column in sys.log_query.
node_idintegerNode ID within the query plan, representing an execution step.
parent_idbigintParent node ID, to show the parent/child relationships among nodes within a single query plan.
indexbigintZero-based index for the nodes in a plan.
typevarchar(255)Type of execution step, such as hash distribute, filter, or limit.
workerstextWorkers processing the plan node: single or all for each node of the query plan
query_plantextQuery plan text for each node

Examples ​

premdb=# select plan_id, node_id, substr(query_plan, 1, 50) 
from sys.log_query_explain order by plan_id, node_id;
                  plan_id                    | node_id |                       substr                       
----------------------------------------------+---------+----------------------------------------------------
 0XeN2C-FjU6lxnkPh9ry4jOuU9Ad+s5O0dNO+T7uOdA= |       0 | SELECT                                            +
                                             |         | (match.matchday, DATEADD(month, 1, match.ma
 0XeN2C-FjU6lxnkPh9ry4jOuU9Ad+s5O0dNO+T7uOdA= |       1 | APPEND SCAN                                       +
                                             |         | (match.matchday, DATEADD(month, 1, mat
 0XeN2C-FjU6lxnkPh9ry4jOuU9Ad+s5O0dNO+T7uOdA= |       2 | | SCAN match                                      +
                                             |         |   (match.matchday, match.atid, match.
 0XeN2C-FjU6lxnkPh9ry4jOuU9Ad+s5O0dNO+T7uOdA= |       3 | |-TRANSPOSE                                       +
                                             |         |   (match.matchday, DATEADD(month, 1, m
 0XeN2C-FjU6lxnkPh9ry4jOuU9Ad+s5O0dNO+T7uOdA= |       4 | |-DISTRIBUTE RANDOM                               +
                                             |         |   (match.matchday, DATEADD(mon
 0XeN2C-FjU6lxnkPh9ry4jOuU9Ad+s5O0dNO+T7uOdA= |       5 |   SCAN ROW STORE                                  +
                                             |         |   (match.matchday, match.htid, ma
 0XeN2C-FjU6lxnkPh9ry4jOuU9Ad+s5O0dNO+T7uOdA= |       6 |   FILTER WHERE ((match.htid::INT4 = $2) AND (match
 1LOUm6k0L5L+xZU-duWpJjzPtHWXsNLIpwNKR4FXL4k= |       0 | WRITE ROW STORE                                   +
                                             |         | (team.atid, team.name)                            +
                                             |         | distribute 
 1LOUm6k0L5L+xZU-duWpJjzPtHWXsNLIpwNKR4FXL4k= |       1 | INSERT INTO awayteam                              +
                                             |         | (team.atid, team.name)                            +
                                             |         | distri
 1LOUm6k0L5L+xZU-duWpJjzPtHWXsNLIpwNKR4FXL4k= |       2 | APPEND SCAN                                       +
                                             |         | (team.atid, team.name)                            +
                                             |         | distribute repl
 1LOUm6k0L5L+xZU-duWpJjzPtHWXsNLIpwNKR4FXL4k= |       3 | | SCAN team                                       +
                                             |         |   (team.atid, team.name)                          +
                                             |         |   distribute 
...

The following example joins to the sys.log_query view to return plan information for a query based on its specific SQL text:

premdb=# select slq.plan_id, slqe.node_id, slqe.type from sys.log_query_explain slqe, sys.log_query slq 
where slq.plan_id=slqe.plan_id and slq.query_text like 'select season_name, numteams, sum%';
                  plan_id                    | node_id |         type          
----------------------------------------------+---------+----------------------
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       0 | SELECT               
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       0 | SELECT               
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       2 | DISTRIBUTE SORT      
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       2 | DISTRIBUTE SORT      
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       3 | SORT                 
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       3 | SORT                 
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       4 | EXPRESSION           
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       4 | EXPRESSION           
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       5 | GROUP BY             
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       5 | GROUP BY             
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       6 | DISTRIBUTE HASH      
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       6 | DISTRIBUTE HASH      
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       7 | GROUP BY PARTIAL     
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |       7 | GROUP BY PARTIAL     
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |      14 | INNER JOIN           
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |      14 | INNER JOIN           
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |      16 | DISTRIBUTE HASH      
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |      16 | DISTRIBUTE HASH      
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |      15 | APPEND SCAN          
 WnbWYG5kKxbVuJOPqBB0p7fd5ViubYLAHFxV0akPIdA= |      15 | APPEND SCAN          
...

Parent topic:System Views