Query Details

The Query Details view displays query metrics, the text of the query, and the query plan and progress for a single query selected from the Query Monitor view.

Query Details

Query Execution Metrics

The Query ID, execution status, and run time of the query are displayed at the top.

The following metrics are displayed for the query.

User
The Greenplum Database role that submitted the query.

Database
The name of the database that was queried.

Res Group or Res Queue
The name of the resource group or resource queue that is managing the query.

Submitted
The time the query was submitted to the query planner.

Queued Time
The amount of time the query has been (or was) in queue awaiting execution.

Run Time
The amount of time since query execution began.

CPU
(Active queries only.) Current CPU percent average for all segment processes executing this query. The percentages for all processes running on each segment are averaged, and then the average of all those values is calculated to render this metric. Current CPU percent average is always zero in historical and tail data. The master and standby master are excluded from the calculation.

CPU Skew
The amount of CPU skew. CPU skew occurs when query executor processes for one segment use a disproportionate amount of CPU compared to processes for other segments executing the query. This value is calculated as

1 − (average segment CPU / maximum segment CPU)

Memory
Memory consumed by all segment processes executing the query.

Spill File Size
The total size of spill files created for the query. Greenplum Database creates spill files when there is insufficient memory to execute the query in memory. See Managing Spill Files Generated by Queries for information about spill files.

Disk R
The current average disk read rate for all segment hosts.

Disk W
The current average disk write rate for all segment hosts.

Blocking
A list of queries blocked by locks held by this query. Click a query ID in the list to see the details for that query.

Blocked by
A list of queries with locks blocking this query. Click a query ID in the list to see the details for that query.

Query Text and Execution Plan

The query text and the query’s plan and execution progress are shown in the lower panels of the Query Details view. The text of the query is displayed in the left panel, and the plan and progress is displayed in the right panel.

Query Plan and Progress

The Plan & Progress tab in the lower right panel is a graphical representation of the query plan with animation and real-time metrics to show execution progress. Each box in the tree represents a step in the query execution plan. The boxes are labeled with the operation they represent and have a CPU usage metric. Query execution begins at the bottom of the tree and ends at the top.

Before a step begins to execute, the box has a solid white fill. When the step becomes active, the box is animated with a green and white fill to indicate that the operator is working. When the step has completed, the box has a solid green fill.

Query execution plans are executed in “slices,” portions of the query plan that segments can work on independently in parallel. The plan is sliced wherever a data motion operator occurs. The time displayed in the upper right corner of each box is the amount of CPU time used for all steps in the slice to which the step belongs. The visual query plan does not illustrate slices, but you can find information about slices in the textual plan.

If you click a step, the box expands to show additional details.

Expanded Step

The expanded plan box contains the following metrics.

  • The type of operator. When the operator is a table scan, the name of the scanned table is included. See Query Plan Execution for descriptions of the operators.
  • Row Skew - the amount of row skew for the current operator, a value from 0% to 100%. Row skew occurs when some segments process more rows than other segments. The percentage is calculated as (1 − (average segment rows / maximum segment rows)) * 100.
  • Estimated Completion - the current percentage of actual rows to estimated rows for this plan step. The percentage can exceed 100% if the operation produces more rows than the optimizer’s estimate. The percentage changes to “Completed” when the operation completes.
  • Actual Rows - The current number of rows produced by this step. Note that for nested join operators, the Actual Rows is estimated since the actual row counts are not available while the join is executing.
  • Estimated Rows - The estimated number of rows the operator will produce.

Textual Plan

Select the Textual Plan tab and click RUN EXPLAIN to generate the text representation of the explain plan.

The RUN EXPLAIN button is dimmed if Command Center is unable to generate the explain plan. Command Center is unable to generate the explain plan if the size of the query text is greater than 100K or if the query text contains multiple statements.

Query Details

The textual plan is the output of the Greenplum Database EXPLAIN command for the query. The query plan steps are labeled with arrows (->) and the structure of the query plan tree is indicated with indentation.

The Optimizer status: line at the bottom of the textual plan reports whether the explain plan was generated using the GPORCA optimizer (PQO) or the legacy query optimizer.

For help reading the textual explain plan see the EXPLAIN command in the Greenplum Database Reference Guide and Query Profiling in the Greenplum Database Administrator Guide. See Query Execution for descriptions of the query operators.