Query Profiling Statistics-AnalyticDB(AnalyticDB)-阿里云帮助中心

When a query runs slowly or behaves unexpectedly, it can be difficult to tell which operator is the bottleneck. Query profiling statistics lets you collect operator-level execution data for every query — time spent, rows processed, and memory and I/O consumed — so you can pinpoint the slow or resource-intensive operator directly.

Note Query profiling statistics requires AnalyticDB for PostgreSQL V6.3.8.2 or later. To update your instance, see Update the minor engine version.

Enable query profiling statistics

Query profiling statistics is disabled by default. Use the queryprofile.enable parameter to control it.

Check the current status:

SHOW queryprofile.enable;

Enable for the current session:

SET queryprofile.enable = ON;

Disable for the current session:

SET queryprofile.enable = OFF;

Enable for an entire database:

ALTER DATABASE <dbname> SET queryprofile.enable = ON;

To enable or disable query profiling statistics at the instance level, to contact technical support.

Query profile views

After you enable query profiling statistics, execution data is available through four system views.

View	Scope	Data
`queryprofile.query_exec_history`	Completed queries	Basic information: query ID, timing, SQL text
`queryprofile.query_exec_status`	Queries currently running	Basic information: query ID, timing, SQL text
`queryprofile.node_exec_history`	Completed queries	Per-operator execution details
`queryprofile.node_exec_status`	Queries currently running	Per-operator execution details

When to use which view:

To investigate a slow query that has already finished, start with queryprofile.query_exec_history to get its queryid, then look up per-operator data in queryprofile.node_exec_history.
To inspect a query that is still running, use queryprofile.query_exec_status and queryprofile.node_exec_status.

query_exec_history and query_exec_status schema

Both views share the same schema.

Field	Data type	Description
`queryid`	int8	Unique query ID
`sessid`	integer	Session ID
`commandid`	integer	Command ID within the session
`starttime`	timestamptz	Query start time
`runtime`	float8	Execution duration, in seconds
`stmt_text`	text	SQL statement text

node_exec_history and node_exec_status schema

Both views share the same schema. Each row represents one operator in the query execution plan.

Field	Data type	Description
`queryid`	int8	Unique query ID
`stmtid`	int8	SQL statement ID
`sessid`	integer	Session ID
`commandid`	integer	Command ID within the session
`nodeid`	integer	Operator ID in the execution plan
`sliceid`	integer	Slice ID in the execution plan
`nodetypeid`	integer	Operator type ID
`nodename`	text	Operator name
`tuplesout`	int8	Rows output by the operator
`tuplesin`	int8	Rows input by the operator
`tuplesplan`	int8	Number of rows input by the operator in the query execution plan
`execmem`	float8	Memory allocated to the operator by the executor
`workmem`	float8	Memory actually used by the operator
`starttime`	timestamptz	Operator execution start time
`endtime`	timestamptz	Operator execution end time
`duration`	float8	Time the operator was actively executing, in seconds. This is not the wall-clock interval between `starttime` and `endtime` — that interval may contain the execution duration of underlying operators.
`diskreadsize`	int8	Data read from disk by the operator
`diskreadtime`	float8	Time spent reading from disk, in seconds
`netiosize`	int8	Data transmitted between nodes
`netiotime`	float8	Time spent on inter-node data transfer, in seconds

Query currently running queries

Get basic information for all running queries:

SELECT * FROM queryprofile.query_exec_status;

Get per-operator execution details for all running queries:

SELECT * FROM queryprofile.node_exec_status;

Query historical queries

Get basic information for all completed queries:

SELECT * FROM queryprofile.query_exec_history;

Get per-operator execution details for all completed queries:

SELECT * FROM queryprofile.node_exec_history;

Configure the update frequency for running-query views

The queryprofile.refresh_interval parameter controls how often queryprofile.query_exec_status and queryprofile.node_exec_status are refreshed. The value is the number of rows an operator must process between two consecutive updates.

The default value is 1000, meaning statistics are updated every 1,000 rows read by operators. A value of 0 disables query profiling statistics.

Check the current setting:

SHOW queryprofile.refresh_interval;

Sample output:

 queryprofile.refresh_interval
-------------------------------
 1000
(1 row)

Note To change the update frequency or disable query profiling statistics, to contact technical support.

Configure historical query profile retention

Two parameters control how historical query profiles are retained and recycled:

Parameter	Default	Description
`queryprofile.max_query_num`	10000	Maximum number of query profiles to keep
`queryprofile.query_time_limit`	1	Queries shorter than this duration (in seconds) are recycled first

Check the current settings:

SHOW queryprofile.max_query_num;
SHOW queryprofile.query_time_limit;

How recycling works: When the number of stored profiles exceeds queryprofile.max_query_num, profiles for queries shorter than queryprofile.query_time_limit seconds are removed first. If no such short queries exist, the oldest profiles are removed.

Note To change these parameters, to contact technical support.

Examples

The following examples show how to use query profiles to diagnose performance issues.

Find the slowest operators in a completed query

Use this workflow to identify which operator is causing a slow query.

Enable query profiling statistics for the current session.
```
SET queryprofile.enable = ON;
```
Find the 10 most recently completed queries and their execution duration.
```
SELECT * FROM queryprofile.query_exec_history ORDER BY starttime DESC LIMIT 10;
```
The following figure shows a sample query result.
Use the queryid from the previous result to retrieve per-operator data for a specific query.
```
SELECT * FROM queryprofile.node_exec_history WHERE queryid = 6902*********93;
```
The following figure shows a sample query result. The result shows each operator's name, execution time, row counts, memory usage, and disk I/O. You can analyze the execution information of each operator to identify and troubleshoot performance issues.