Query diagnostics
The query diagnostics feature in AnalyticDB for PostgreSQL lets you view execution plans and their statistics to analyze and tune queries.
Prerequisites
The instance is in elastic storage mode and runs the minor engine version of V6.3.10.1 or later. For information about how to view and update the minor engine version of an instance, see View the minor version and Version upgrade.Usage notes
The Query Diagnostics page displays only SQL queries with a duration of more than one second.
Procedure
- Log on to the AnalyticDB for PostgreSQL console.
- In the upper-left corner of the console, select a region.
- Find the instance that you want to manage and click the instance ID.
In the left-side navigation pane, choose .
On the Query Diagnostics tab, use the controls in the upper-right corner to select a database, apply filters, and specify a time range.
The Query Analysis page provides two views for query execution information: the query execution swimlane diagram and the SQL Queries.
Query execution swimlane diagram
Each colored block in the swimlane represents a query execution process. You can hover over a colored block to view information about its execution, and click Details to go to the Query Properties page. For a description of the Query Properties page, see Query Properties.
SQL Queries
Each item in the list represents a query execution process. The list displays relevant information and data about the query execution. In the Actions column, you can click Diagnose to go to the Query Properties page. For a description of the Query Properties page, see Query Properties.
The SQL list contains the following information:
Parameter
Description
SQL
The SQL query statement.
Start time
The time when the query started.
Query ID
The unique ID of the query.
Session ID
The ID of the session to which the query belongs.
Database
The name of the database that is queried.
Status
The execution status of the query. Valid values:
Completed
Running
Duration
The total execution duration of the query, in seconds (s).
Username
The user who ran the query.
You can click the Download button to download the list to your computer.
Query properties
The Query Properties page consists of three tabs: Query Statement, Execution Plan Text, and Execution Plan Tree.
Query statement
The Query Statement tab provides the SQL query statement.
Execution plan text
The Execution Plan Text tab provides the execution plan obtained by using the EXPLAIN command. For more information about execution plans, see Execution Plans.
Execution plan tree
The Execution Plan Tree tab provides an interactive tree diagram of the execution plan. This tab displays the plan tree on the left and the operators' execution statistics on the right. You can click an operator card in the plan tree to view its detailed statistics.
By default, the line below each operator card in the plan tree represents the operator's share of the total execution duration. The list in the upper-right corner also displays operators sorted by their share of the execution duration. You can also choose to sort operators and calculate their proportional share based on memory usage, disk read data size, disk read duration, network data transfer size, or network transfer duration.
The following table describes the operator statistics.
Parameter | Description |
Operator Name | The name and ID of the operator, which is unique within the plan tree. |
Type ID | The ID of the operator type. |
Type | The name of the operator type, which may not be unique within the plan tree. |
Output Rows | The number of rows the operator outputs to its parent. |
Input Rows | The number of rows the operator reads from its child operators or from disk. |
Memory Used by Execution | The memory the operator consumes during execution. Note Query Analysis tracks memory usage for only the |
Execution Duration | The execution duration of the operator. |
Amount of Disk Read Data | The total amount of data the operator reads from disk. Note Data read from disk includes raw data read by |
Disk Read Duration | The time spent by the operator reading data from disk. |
Amount of Transmitted Data | The total amount of data transferred between nodes by a |
Data Transmission Duration | The time spent by a |