RedShift Visual Explain

EXPLAIN SELECT * FROM  svl_qlog

• pg_catalog.stl_explain
• pg_catalog.stl_plan_info 
• pg_catalog.svl_query_summary

Contents

• Vendor Specific Information
  • Executing Visual Explain in Aqua Data Studio
    • In Query Analyzer
      • Show Execution Plan Button
      • Execute Explain Button
  • In DBA Tools Instance Manager
    • Query History

Vendor Specific Information

• Operation - Plan nodes that refer to the execution on compute nodes. Alert messages from the database will also be displayed in this column. For joins, it also specifies a method for how data will be moved around a cluster to facilitate the join. This data movement can be either a broadcast or a redistribution. It also references inner and outer tables. Additional information can be found here.
• Additional Operation Info - Qualifier and filter information for the operation.
• *Total Cost - The estimated relative cost of executing the step.
• *Startup Cost - The estimated relative cost of returning the first row for this step.
• Rows Produced - The estimated number of rows that will be produced by the step.
• Bytes Produced - The estimated number of bytes that will be produced by the step. 
• Width - Number of bytes per row.
• Maximum Time(uS) - Maximum amount of time for the step(s) to execute (in microseconds). 
• Maximum Time(uS) - Maximum amount of time for the step(s) to execute (in microseconds). 
• Average Time(uS) - Average time for the step(s) to execute (in microseconds). 
• % of Total Max Time - Percentage of the total maximum amount of time for all steps.
• % of Total Avg Time - Percentage of the total average amount of time for all steps.
• Work Memory - Amount of working memory (in bytes) assigned to the query step(s). 
• Rows Pre-filtered - For scans of permanent tables, the total number of rows emitted before filtering rows marked for deletion (ghost rows).
• Step Labels -  Consists of the query step name(s) and, when applicable, table ID and table name. A list of step labels and their associated function can be found here.
• Step Location - Location where the step executes, compute node or leader node. The leader node receives the query and parses the SQL. The compute node slices and executes the query segments in parallel. When the compute nodes are done, they return the query results to the leader node for final processing.
• QueryNo - The number assigned to this query.

Executing Visual Explain in Aqua Data Studio

In Query Analyzer

Show Execution Plan Button

Turning ON this button during normal query execution will return the query data result set as well as the visual explain execution plan with a diagram. The execution plan contains the actual cost for running the query.

Execute Explain Button

The information returned for this query execution will be a text version of the explain plan and not the visual explain plan grid with diagram. In this case, no data is returned because the query is never executed. The plan describes the access path that will get used when the query is executed. It only shows the plan that Redshift will execute if the query is run under current operating conditions. If you change the schema or data for a table and run the analyze command again to update the statistical metadata, the query plan might be different. 

In DBA Tools Instance Manager

Query History

• Type = Query – Lets you scroll through queries that were executed based on your filter selection. 
  
• Type = All - Lets you scroll through a list which contains queries, DDL and utility statements. This works similarly to the query list described above. However, this list also contains DDL and utility statements and there is no execution plan for them. You will notice that when you select one of these the “Execution Plan” tab disappears. The execution plan tab is only available for queries.
  
  In the case where visual explain is executed, an explain plan grid with detailed summarized information about each step in the query process will be displayed. This plan grid should be read from the bottom up. When you right-click on this grid, a pop-up menu will show you some other options that are available such as an explain diagram. Clicking on the “Explain Diagram” will show a block hierarchical diagram of the steps for executing this query.
  
  
• Type = Utility or DDL – Does not apply since there are no queries in these lists.