The EXPLAIN ANALYZE
statement executes a SQL query and generates a statement plan with execution statistics. Statement plans provide information around SQL execution, which can be used to troubleshoot slow queries by figuring out where time is being spent, how long a processor (i.e., a component that takes streams of input rows and processes them according to a specification) is not doing work, etc. The (DISTSQL)
option returns the statement plan and performance statistics as well as a generated link to a graphical distributed SQL physical statement plan tree. For more information about distributed SQL queries, see the DistSQL section of our SQL layer architecture docs. The (DEBUG)
option generates a URL to download a bundle with more details about the statement plan for advanced debugging.
The generated physical statement plan is encoded into a byte string after the fragment identifier (#
) in the generated URL. The fragment is not sent to the web server; instead, the browser waits for the web server to return a decode.html
resource, and then JavaScript on the web page decodes the fragment into a physical statement plan diagram. The statement plan is, therefore, not logged by a server external to the CockroachDB cluster and not exposed to the public internet.
Aliases
EXPLAIN ANALYSE
is an alias for EXPLAIN ANALYZE
.
Synopsis
Parameters
Parameter | Description |
---|---|
PLAN |
(Default) Execute the statement and return a statement plan with planning and execution time for an explainable statement. See PLAN option. |
VERBOSE |
Execute the statement and show as much information as possible about the statement plan. |
TYPES |
Execute the statement and include the intermediate data types CockroachDB chooses to evaluate intermediate SQL expressions. |
DEBUG |
Execute the statement and generate a ZIP file containing files with detailed information about the query and the database objects referenced in the query. See DEBUG option. |
REDACT |
Execute the statement and redact constants, literal values, parameter values, and personally identifiable information (PII) from the output. See REDACT option. |
DISTSQL |
Execute the statement and return a statement plan and performance statistics as well as a generated link to a graphical distributed SQL physical statement plan tree. See DISTSQL option. |
preparable_stmt |
The statement you want to execute and analyze. All preparable statements are explainable. |
Required privileges
To generate a statement bundle, you must have the privileges to execute the SQL statement, as well as the privileges required to collect the statement bundle.
To find the minimum required privileges for a SQL statement, refer to the SQL reference documentation for the statement.
A user with the VIEWACTIVITY
system privilege can generate a bundle for any statement. To grant this privilege, issue the following SQL commands. Replace {user}
with the user's ID.
ALTER USER {user} WITH VIEWACTIVITY;
GRANT SYSTEM VIEWSYSTEMTABLE TO {user};
Success responses
A successful EXPLAIN ANALYZE
statement returns a table with the following details in the info
column:
Detail | Description |
---|---|
Global properties | The properties and statistics that apply to the entire statement plan. |
Statement plan tree properties | A tree representation of the hierarchy of the statement plan. |
Node details | The properties, columns, and ordering details for the current statement plan node in the tree. |
Time | The time details for the statement. The total time is the planning and execution time of the statement. The execution time is the time it took for the final statement plan to complete. The network time is the amount of time it took to distribute the statement across the relevant nodes in the cluster. Some statements do not need to be distributed, so the network time is 0ms. |
If you use the DISTSQL
option, the statement will also return a URL generated for a physical statement plan that provides high level information about how a statement will be executed. The generated physical statement plan is encoded into a byte string after the fragment identifier (#
) in the generated URL. The fragment is not sent to the web server; instead, the browser waits for the web server to return a decode.html
resource, and then JavaScript on the web page decodes the fragment into a physical statement plan diagram. The statement plan is, therefore, not logged by a server external to the CockroachDB cluster and not exposed to the public internet.
For details about reading the physical statement plan, see DistSQL plan diagram.
If you use the DEBUG
option, the statement will return only a URL and instructions to download the DEBUG
bundle, which includes the physical statement plan.
Global properties
Property | Description |
---|---|
planning time |
The total time the planner took to create a statement plan. |
execution time |
The time it took for the final statement plan to complete. |
distribution |
Whether the statement was distributed or local. If distribution is full , execution of the statement is performed by multiple nodes in parallel, then the results are returned by the gateway node. If local , the execution plan is performed only on the gateway node. Even if the execution plan is local , row data may be fetched from remote nodes, but the processing of the data is performed by the local node. |
vectorized |
Whether the vectorized execution engine was used in this statement. |
rows read from KV |
The number of rows read from the storage layer. |
cumulative time spent in KV |
The total amount of time spent in the storage layer. |
cumulative time spent due to contention |
The total amount of time this statement spent waiting in contention. |
maximum memory usage |
The maximum amount of memory used by this statement anytime during its execution. |
network usage |
The amount of data transferred over the network while the statement was executed. If the value is 0 B, the statement was executed on a single node and didn't use the network. |
sql cpu time |
The total amount of time spent in the SQL layer. It does not include time spent in the storage layer. |
regions |
The regions where the affected nodes were located. |
max sql temp disk usage |
(DISTSQL option only) How much disk spilling occurs when executing a query. This property is displayed only when the disk usage is greater than zero. |
estimated RUs consumed |
The estimated number of Request Units (RUs) consumed by the statement. This property is visible only on CockroachDB Serverless clusters. |
Statement plan tree properties
Statement plan tree properties | Description |
---|---|
processor |
Each processor in the statement plan hierarchy has a node with details about that phase of the statement. For example, a statement with a GROUP BY clause has a group processor with details about the cluster nodes, rows, and operations related to the GROUP BY operation. |
nodes |
The names of the CockroachDB cluster nodes affected by this phase of the statement. |
regions |
The regions where the affected nodes were located. |
actual row count |
The actual number of rows affected by this processor during execution. |
vectorized batch count |
When the vectorized execution engine is used, the number of batches of column data that are processed by the vectorized engine. |
KV time |
The total time this phase of the statement was in the storage layer. |
KV contention time |
The time the storage layer was in contention during this phase of the statement. |
KV rows read |
During scans, the number of rows in the storage layer read by this phase of the statement. |
KV bytes read |
During scans, the amount of data read from the storage layer during this phase of the statement. |
KV gRPC calls |
During scans, the number of gRPC calls made between nodes during this phase of the statement. |
estimated max memory allocated |
The estimated maximum allocated memory for a statement. |
estimated max sql temp disk usage |
The estimated maximum temporary disk usage for a statement. |
MVCC step count (ext/int) |
The number of times that the underlying storage iterator stepped forward during the work to serve the operator's reads, including stepping over MVCC keys that could not be used in the scan. |
MVCC seek count (ext/int) |
The number of times that the underlying storage iterator jumped (seeked) to a different data location. |
sql cpu time |
The total time this phase of the statement was in the SQL layer. It does not include time spent in the storage layer. |
estimated row count |
The estimated number of rows affected by this processor according to the statement planner, the percentage of the table the query spans, and when the statistics for the table were last collected. |
table |
The table and index used in a scan operation in a statement, in the form {table name}@{index name} . |
spans |
The interval of the key space read by the processor. FULL SCAN indicates that the table is scanned on all key ranges of the index (also known as a "full table scan" or "unlimited full scan"). FULL SCAN (SOFT LIMIT) indicates that a full table scan can be performed, but will halt early once enough rows have been scanned. LIMITED SCAN indicates that the table will be scanned on a subset of key ranges of the index. [/1 - /1] indicates that only the key with value 1 is read by the processor. |
PLAN
option
By default, EXPLAIN ANALYZE
uses the PLAN
option. EXPLAIN ANALYZE
and EXPLAIN ANALYZE (PLAN)
produce the same output.
PLAN
suboptions
The PLAN
suboptions VERBOSE
and TYPES
described in EXPLAIN
options are also supported. For an example, see EXPLAIN ANALYZE (VERBOSE)
.
DISTSQL
option
EXPLAIN ANALYZE (DISTSQL)
generates a physical statement in the plan diagram. The DistSQL plan diagram displays the physical statement plan, as well as execution statistics. The statistics listed depend on the query type and the execution engine used. If the query contains subqueries or post-queries there will be multiple diagrams.
You can use EXPLAIN ANALYZE (DISTSQL)
only as the top-level statement in a query.
DistSQL plan diagram
The graphical plan diagram displays the processors and operations that make up the statement plan. While the text output from the PLAN
option shows the statement plan across the cluster, the DISTSQL
option shows details on each node involved in the query.
Field | Description | Execution engine |
---|---|---|
<Processor>/<id> | The processor and processor ID used to read data into the SQL execution engine. A processor is a component that takes streams of input rows, processes them according to a specification, and outputs one stream of rows. For example, a TableReader processor reads in data, and an Aggregator aggregates input rows. |
Both |
<table>@<index> | The index used by the processor. | Both |
Spans | The interval of the key space read by the processor. For example, [/1 - /1] indicates that only the key with value 1 is read by the processor. |
Both |
Out | The output columns. | Both |
KV time | The total time this phase of the query was in the storage layer. | Both |
KV contention time | The time the storage layer was in contention during this phase of the query. | Both |
KV rows read | During scans, the number of rows in the storage layer read by this phase of the query. | Both |
KV bytes read | During scans, the amount of data read from the storage layer during this phase of the query. | Both |
cluster nodes | The names of the CockroachDB cluster nodes involved in the execution of this processor. | Both |
batches output | The number of batches of columnar data output. | Vectorized engine only |
rows output | The number of rows output. | Vectorized engine only |
IO time | How long the TableReader processor spent reading data from disk. | Vectorized engine only |
stall time | How long the processor spent not doing work. This is aggregated into the stall time numbers as the query progresses down the tree (i.e., stall time is added up and overlaps with previous time). | Row-oriented engine only |
bytes read | The size of the data read by the processor. | Both |
rows read | The number of rows read by the processor. | Both |
@<n> | The index of the column relative to the input. | Both |
max memory used | How much memory (if any) is used to buffer rows. | Row-oriented engine only |
max disk used | How much disk (if any) is used to buffer data. Routers and processors will spill to disk buffering if there is not enough memory to buffer the data. | Row-oriented engine only |
execution time | How long the engine spent executing the processor. | Vectorized engine only |
max vectorized memory allocated | How much memory is allocated to the processor to buffer batches of columnar data. | Vectorized engine only |
max vectorized disk used | How much disk (if any) is used to buffer columnar data. Processors will spill to disk buffering if there is not enough memory to buffer the data. | Vectorized engine only |
left(@<n>)=right(@<n>) | The equality columns used in the join. | Both |
stored side | The smaller table that was stored as an in-memory hash table. | Both |
rows routed | How many rows were sent by routers, which can be used to understand network usage. | Row-oriented engine only |
network latency | The latency time in nanoseconds between nodes in a stream. | Vectorized engine only |
bytes sent | The number of actual bytes sent (i.e., encoding of the rows). This is only relevant when doing network communication. | Both |
Render | The stage that renders the output. | Both |
by hash | (Orange box) The router, which is a component that takes one stream of input rows and sends them to a node according to a routing algorithm. For example, a hash router hashes columns of a row and sends the results to the node that is aggregating the result rows. |
Both |
unordered / ordered | (Blue box) A synchronizer that takes one or more output streams and merges them to be consumable by a processor. An ordered synchronizer is used to merge ordered streams and keeps the rows in sorted order. | Both |
<data type> | If you specify EXPLAIN (DISTSQL, TYPES) , lists the data types of the input columns. |
Both |
Response | The response back to the client. | Both |
DEBUG
option
EXPLAIN ANALYZE (DEBUG)
executes a query and generates a link to a ZIP file that contains the physical statement plan, execution statistics, statement tracing, and other information about the query.
File | Description |
---|---|
stats-{table}.sql |
Contains statistics for a table in the query. |
schema.sql |
Contains CREATE statements for objects in the query. |
env.sql |
Contains information about the CockroachDB environment. |
trace.txt |
Contains statement traces in plaintext format. |
trace.json |
Contains statement traces in JSON format. |
trace-jaeger.json |
Contains statement traces in JSON format that can be imported to Jaeger. |
distsql.html |
The query's physical statement plan. This diagram is identical to the one generated by EXPLAIN (DISTSQL) . |
plan.txt |
The query execution plan. This is identical to the output of EXPLAIN (VERBOSE) . |
opt.txt |
The statement plan tree generated by the cost-based optimizer. This is identical to the output of EXPLAIN (OPT) . |
opt-v.txt |
The statement plan tree generated by the cost-based optimizer, with cost details. This is identical to the output of EXPLAIN (OPT, VERBOSE) . |
opt-vv.txt |
The statement plan tree generated by the cost-based optimizer, with cost details and input column data types. This is identical to the output of EXPLAIN (OPT, TYPES) . |
vec.txt |
The statement plan tree generated by the vectorized execution engine. This is identical to the output of EXPLAIN (VEC) . |
vec-v.txt |
The statement plan tree generated by the vectorized execution engine. This is identical to the output of EXPLAIN (VEC, VERBOSE) . |
statement.txt |
The SQL statement for the query. |
You can obtain this ZIP file by following the link provided in the EXPLAIN ANALYZE (DEBUG)
output, or by activating statement diagnostics in the DB Console.
To allow or disallow a role from seeing statements diagnostics bundles, set the
VIEWACTIVITYREDACTED
role option.
In CockroachDB v21.2.x, v22.1.0 to v22.1.16, v22.2.0 to v22.2.6, non-admin SQL users with an authenticated HTTP session could download statement diagnostic bundles given a bundle URL from the DB Console or the
EXPLAIN ANALYZE (DEBUG)
statement with a valid HTTP session cookie. This has been resolved in v22.1.17 and v22.2.7. For more information, see the Technical Advisory A99049.
REDACT
option
EXPLAIN ANALYZE (REDACT)
executes a query and causes constants, literal values, parameter values, and personally identifiable information (PII) to be redacted as ‹×›
in the output.
You can use the REDACT
flag in combination with the PLAN
option (including the VERBOSE
and TYPES
suboptions) to redact sensitive values in the physical statement plan, and with the DEBUG
option to redact values in the statement bundle.
For an example, see EXPLAIN ANALYZE (REDACT)
.
Examples
The following examples use the movr
example dataset.
Start the MovR database on a 3-node CockroachDB demo cluster with a larger data set.
cockroach demo movr --num-histories 250000 --num-promo-codes 250000 --num-rides 125000 --num-users 12500 --num-vehicles 3750 --nodes 3
EXPLAIN ANALYZE
Use EXPLAIN ANALYZE
without an option, or equivalently with the PLAN
option, to execute a query and display the physical statement plan with execution statistics.
For example, the following EXPLAIN ANALYZE
statement executes a simple query against the MovR database and then displays the physical statement plan with execution statistics:
EXPLAIN ANALYZE SELECT city, AVG(revenue) FROM rides GROUP BY city;
info
--------------------------------------------------------------------------------------
planning time: 899µs
execution time: 8ms
distribution: full
vectorized: true
rows decoded from KV: 500 (88 KiB, 1 gRPC calls)
cumulative time spent in KV: 6ms
maximum memory usage: 240 KiB
network usage: 0 B (0 messages)
regions: us-east1
sql cpu time: 2ms
estimated RUs consumed: 0
isolation level: serializable
priority: normal
quality of service: regular
• group (streaming)
│ nodes: n1
│ regions: us-east1
│ actual row count: 9
│ sql cpu time: 1ms
│ estimated row count: 9
│ group by: city
│ ordered: +city
│
└── • scan
nodes: n1
regions: us-east1
actual row count: 500
KV time: 6ms
KV contention time: 0µs
KV rows decoded: 500
KV bytes read: 88 KiB
KV gRPC calls: 1
estimated max memory allocated: 130 KiB
sql cpu time: 469µs
estimated row count: 500 (100% of the table; stats collected 35 minutes ago)
table: rides@rides_pkey
spans: FULL SCAN
(38 rows)
If you perform a join, the estimated max memory allocation is also reported for the join. For example:
EXPLAIN ANALYZE SELECT * FROM vehicles JOIN rides ON rides.vehicle_id = vehicles.id and rides.city = vehicles.city limit 100;
info
------------------------------------------------------------------------------------------
planning time: 1ms
execution time: 5ms
distribution: local
vectorized: true
rows decoded from KV: 515 (90 KiB, 2 gRPC calls)
cumulative time spent in KV: 4ms
maximum memory usage: 580 KiB
network usage: 0 B (0 messages)
regions: us-east1
sql cpu time: 729µs
estimated RUs consumed: 0
isolation level: serializable
priority: normal
quality of service: regular
• limit
│ count: 100
│
└── • hash join
│ nodes: n1
│ regions: us-east1
│ actual row count: 100
│ estimated max memory allocated: 310 KiB
│ estimated max sql temp disk usage: 0 B
│ sql cpu time: 223µs
│ estimated row count: 56
│ equality: (vehicle_id, city) = (id, city)
│ right cols are key
│
├── • scan
│ nodes: n1
│ regions: us-east1
│ actual row count: 500
│ KV time: 2ms
│ KV contention time: 0µs
│ KV rows decoded: 500
│ KV bytes read: 88 KiB
│ KV gRPC calls: 1
│ estimated max memory allocated: 250 KiB
│ sql cpu time: 467µs
│ estimated row count: 500 (100% of the table; stats collected 36 minutes ago)
│ table: rides@rides_pkey
│ spans: FULL SCAN
│
└── • scan
nodes: n1
regions: us-east1
actual row count: 15
KV time: 2ms
KV contention time: 0µs
KV rows decoded: 15
KV bytes read: 2.4 KiB
KV gRPC calls: 1
estimated max memory allocated: 20 KiB
sql cpu time: 40µs
estimated row count: 15 (100% of the table; stats collected 36 minutes ago)
table: vehicles@vehicles_pkey
spans: FULL SCAN
(58 rows)
EXPLAIN ANALYZE (VERBOSE)
Use the VERBOSE
suboption of PLAN
to execute a query and display the physical statement plan with additional execution statistics.
EXPLAIN ANALYZE (VERBOSE) SELECT city, AVG(revenue) FROM rides GROUP BY city;
info
--------------------------------------------------------------------------------------
planning time: 1ms
execution time: 5ms
distribution: full
vectorized: true
rows decoded from KV: 500 (88 KiB, 500 KVs, 1 gRPC calls)
cumulative time spent in KV: 4ms
maximum memory usage: 240 KiB
network usage: 0 B (0 messages)
regions: us-east1
sql cpu time: 622µs
estimated RUs consumed: 0
isolation level: serializable
priority: normal
quality of service: regular
• group (streaming)
│ columns: (city, avg)
│ nodes: n1
│ regions: us-east1
│ actual row count: 9
│ vectorized batch count: 1
│ sql cpu time: 137µs
│ estimated row count: 9
│ aggregate 0: avg(revenue)
│ group by: city
│ ordered: +city
│
└── • scan
columns: (city, revenue)
ordering: +city
nodes: n1
regions: us-east1
actual row count: 500
vectorized batch count: 1
KV time: 4ms
KV contention time: 0µs
KV rows decoded: 500
KV pairs read: 500
KV bytes read: 88 KiB
KV gRPC calls: 1
estimated max memory allocated: 130 KiB
sql cpu time: 485µs
MVCC step count (ext/int): 500/500
MVCC seek count (ext/int): 9/9
estimated row count: 500 (100% of the table; stats collected 36 minutes ago)
table: rides@rides_pkey
spans: FULL SCAN
(47 rows)
EXPLAIN ANALYZE (DISTSQL)
Use EXPLAIN ANALYZE (DISTSQL)
to execute a query, display the physical statement plan with execution statistics, and generate a link to a graphical DistSQL statement plan.
EXPLAIN ANALYZE (DISTSQL) SELECT city, AVG(revenue) FROM rides GROUP BY city;
info
------------------------------------------------------------------------------------
planning time: 822µs
execution time: 4ms
distribution: full
vectorized: true
rows decoded from KV: 500 (88 KiB, 1 gRPC calls)
cumulative time spent in KV: 3ms
maximum memory usage: 240 KiB
network usage: 0 B (0 messages)
regions: us-east1
sql cpu time: 407µs
estimated RUs consumed: 0
isolation level: serializable
priority: normal
quality of service: regular
• group (streaming)
│ nodes: n1
│ regions: us-east1
│ actual row count: 9
│ sql cpu time: 82µs
│ estimated row count: 9
│ group by: city
│ ordered: +city
│
└── • scan
nodes: n1
regions: us-east1
actual row count: 500
KV time: 3ms
KV contention time: 0µs
KV rows decoded: 500
KV bytes read: 88 KiB
KV gRPC calls: 1
estimated max memory allocated: 130 KiB
sql cpu time: 324µs
estimated row count: 500 (100% of the table; stats collected 36 minutes ago)
table: rides@rides_pkey
spans: FULL SCAN
Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#eJyUk99O4zoQxu...
(40 rows)
To view the DistSQL plan diagram, open the URL following Diagram. For an example, see DISTSQL
option.
EXPLAIN ANALYZE (DEBUG)
Use the DEBUG
option to generate a ZIP file containing files with information about the query and the database objects referenced in the query. For example:
EXPLAIN ANALYZE (DEBUG) SELECT city, AVG(revenue) FROM rides GROUP BY city;
info
-----------------------------------------------------------------------------------
Statement diagnostics bundle generated. Download using the SQL shell or command
line.
SQL shell: \statement-diag download 938793171367755777
Command line: cockroach statement-diag download 938793171367755777
(4 rows)
To download the ZIP file containing the statement diagnostics, run the \statement-diag download
or cockroach statement-diag download
commands. You can also obtain the bundle by activating statement diagnostics in the DB Console.
EXPLAIN ANALYZE (REDACT)
Use the REDACT
option to execute a query and cause constants, literal values, parameter values, and personally identifiable information (PII) to be redacted as ‹×›
in the physical statement plan or statement bundle.
EXPLAIN ANALYZE (REDACT) SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;
info
------------------------------------------------------------------------------------------
planning time: 872µs
execution time: 6ms
distribution: full
vectorized: true
rows decoded from KV: 500 (88 KiB, 1 gRPC calls)
cumulative time spent in KV: 4ms
maximum memory usage: 280 KiB
network usage: 0 B (0 messages)
regions: us-east1
sql cpu time: 2ms
estimated RUs consumed: 0
isolation level: serializable
priority: normal
quality of service: regular
• sort
│ nodes: n1
│ regions: us-east1
│ actual row count: 36
│ estimated max memory allocated: 30 KiB
│ estimated max sql temp disk usage: 0 B
│ sql cpu time: 89µs
│ estimated row count: 52
│ order: +revenue
│
└── • filter
│ nodes: n1
│ regions: us-east1
│ actual row count: 36
│ sql cpu time: 1ms
│ estimated row count: 52
│ filter: revenue > ‹×›
│
└── • scan
nodes: n1
regions: us-east1
actual row count: 500
KV time: 4ms
KV contention time: 0µs
KV rows decoded: 500
KV bytes read: 88 KiB
KV gRPC calls: 1
estimated max memory allocated: 250 KiB
sql cpu time: 653µs
estimated row count: 500 (100% of the table; stats collected 38 minutes ago)
table: rides@rides_pkey
spans: FULL SCAN
(47 rows)
In the preceding output, the revenue
comparison value is redacted as ‹×›
.