EXPLAIN ANALYZE

The EXPLAIN ANALYZE 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 . The (DEBUG) option generates a URL to download a bundle with more details about the statement plan for advanced debugging.

EXPLAIN ANALYZE executes the target statement and may modify or delete data. It is not a dry run.If you use EXPLAIN ANALYZE with a statement that changes data or acquires locks, those effects occur when the statement runs. For example, EXPLAIN ANALYZE DELETE FROM t WHERE id = 1; deletes matching rows and then reports execution statistics.To inspect a statement plan without executing the statement, use instead. For example, EXPLAIN DELETE FROM t WHERE id = 1; shows the plan without deleting data.

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 . 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 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 you want to execute and analyze. All preparable statements are explainable.

Required privileges

To generate a statement bundle, you must have the 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 for the statement. A user with the VIEWACTIVITY can generate a bundle for any statement. To grant this privilege, issue the following SQL commands. Replace 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.
Routine body plans	The body statement plans of user-defined functions and stored procedures invoked by the statement.
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.
`plan type`	The plan type used by the query: `generic, re-optimized`, `generic, reused`, or `custom`. For details, refer to .
`statement hints`	The number of from `system.statement_hints` loaded for the statement. If any hints were not applied, the field shows the applied and skipped counts, such as `statement hints: 2 (1 applied, 1 skipped)`. This field is only displayed when the statement has hints. With the `VERBOSE` suboption, the output also includes a tree of applied and skipped hints showing each hint’s type, configuration, and skip reason, if applicable.
`vectorized`	Whether the was used in this statement.
`rows decoded from KV`	The number of rows read from the .
`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 .
`cumulative time spent waiting in admission control`	The total amount of time this statement spent waiting in . This includes time spent in . This property is displayed only when the wait time is greater than zero.
`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.
`regions`	The where the affected nodes were located.
`kv cpu time`	The total amount of time spent in the on the critical path of serving the query. Excludes time spent on asynchronous replication and in the . This property is displayed only with the `VERBOSE` suboption.
`sql cpu time`	The cumulative for this statement execution. This property is displayed only with the `VERBOSE` suboption.
`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 consumed by the statement. This property is visible only on CockroachDB Basic clusters.
`isolation level`	The at which this statement executed.
`priority`	The at which this statement executed.
`quality of service`	The session’s level at which the statement executed.
`historical`	The timestamp and , if applicable, for .
`table stats mode`	Whether the execution plan for the statement was determined using . This field is only displayed when canary statistics are enabled.

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.
`sql nodes`	The names of CockroachDB SQL nodes that were chosen to perform additional manipulation (like filtering and joins) over the rows affected by this SQL statement. This always includes the gateway node.
`kv nodes`	The names of CockroachDB nodes that had to perform reads from disk to fetch necessary data for this SQL statement.
`regions`	The where the affected nodes were located.
`used follower read`	Whether the query used a .
`actual row count`	The actual number of rows affected by this processor during execution.
`vectorized batch count`	When the 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 .
`KV contention time`	The time the was in contention during this phase of the statement.
`KV rows read`	During scans, the number of rows in the read by this phase of the statement.
`KV bytes read`	During scans, the amount of data read from the during this phase of the statement.
`KV gRPC calls`	During scans, the number of 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 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 CPU time consumed by SQL execution goroutines in this phase, excluding CPU time spent in local KV calls.
`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.
`canary window`	The amount of time new statistics for the table remain in before being promoted to stable status. This field is only displayed when canary statistics are enabled.

Routine body plans

If the statement invokes a routine (a or ) whose body is built at execution time, the statement plan tree includes a routine section below the main statement plan. Each routine section contains a body stmt section for each statement in the routine body, with the same node details as the main statement plan. For an example, refer to View routine body plans.

Routine body plan property	Description
`name`	The name of the routine.
`invocations`	The number of times the routine was invoked with this body plan during statement execution.
`plan variant`	The position of this body plan among the distinct body plans produced for the routine, in the form `{N} of {M}`. This property is displayed only when invocations of the routine produce more than one distinct body plan. For example, different argument values can produce different plans for the same body statement. Each plan variant appears in a separate `routine` section with its own invocation count.
`body stmt`	A statement in the routine body. The `sql` property displays the SQL text of the body statement.

The following apply to routine body plans:

A routine that invokes another routine produces a separate routine section for each routine.
do not produce a routine section, because the function body is merged into the outer statement plan.
and do not display routine body plans. shows the routine body inline in the optimizer tree, but in a generic form with the routine’s parameters as columns. This form does not reflect the argument-specialized, optimized body plans shown by EXPLAIN ANALYZE.
Routines run by BEFORE are displayed in the output. Routines run by AFTER triggers are not displayed.
For routines written in , body statements within control-flow constructs, such as IF branches, loops, and exception handlers, are not displayed.
The execution statistics shown on a body statement’s nodes, such as KV time and actual row count, reflect the routine’s first invocation rather than the aggregate across all invocations within the statement.

`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 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 . 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 .	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 , lists the data types of the input columns.	Both
Response	The response back to the client.	Both

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.

`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 for a table in the query.
`schema.sql`	Contains statements for objects in the query.
`env.sql`	Contains information about the CockroachDB environment.
`trace.txt`	Contains in plaintext format.
`trace.json`	Contains statement traces in JSON format.
`trace-jaeger.json`	Contains statement traces in JSON format that can be .
`distsql.html`	The query’s physical statement plan. This diagram is identical to the one generated by .
`plan.txt`	The query execution plan. This is identical to the output of .
`opt.txt`	The statement plan tree generated by the . This is identical to the output of .
`opt-v.txt`	The statement plan tree generated by the cost-based optimizer, with cost details. This is identical to the output of .
`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 .
`vec.txt`	The statement plan tree generated by the engine. This is identical to the output of .
`vec-v.txt`	The statement plan tree generated by the vectorized execution engine. This is identical to the output of .
`statement.txt`	The SQL statement for the query.

If the statement invokes a routine, the plan.txt and opt-vv.txt files in the bundle include the routine body plans inline under the routine. You can obtain this ZIP file by following the link provided in the EXPLAIN ANALYZE (DEBUG) output, or by activating in the DB Console.

In CockroachDB v24.3.1 and later, admin users or SQL users with the VIEWACTIVITY system privilege can choose to redact user data from the Activate statement diagnostics dialog.

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 . Start the 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 and then displays the physical statement plan with execution statistics:

EXPLAIN ANALYZE SELECT city, AVG(revenue) FROM rides GROUP BY city;

                                       info
----------------------------------------------------------------------------------
  planning time: 4ms
  execution time: 5ms
  distribution: full
  vectorized: true
  plan type: custom
  rows decoded from KV: 500 (87 KiB, 1 gRPC calls)
  cumulative time spent in KV: 4ms
  maximum memory usage: 240 KiB
  network usage: 0 B (0 messages)
  regions: us-east1
  kv cpu time: 1ms
  sql cpu time: 443µs
  estimated RUs consumed: 0
  isolation level: serializable
  priority: normal
  quality of service: regular

  • group (streaming)
  │ sql nodes: n1
  │ regions: us-east1
  │ actual row count: 9
  │ sql cpu time: 237µs
  │ estimated row count: 9
  │ group by: city
  │ ordered: +city
  │
  └── • scan
        sql nodes: n1
        kv nodes: n1
        regions: us-east1
        actual row count: 500
        KV time: 4ms
        KV contention time: 0µs
        KV rows decoded: 500
        KV bytes read: 87 KiB
        KV gRPC calls: 1
        estimated max memory allocated: 130 KiB
        sql cpu time: 205µs
        estimated row count: 500 (100% of the table; stats collected 4 days ago)
        table: rides@rides_pkey
        spans: FULL SCAN
(40 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: 2ms
  execution time: 7ms
  distribution: local
  vectorized: true
  plan type: custom
  rows decoded from KV: 515 (90 KiB, 2 gRPC calls)
  cumulative time spent in KV: 6ms
  maximum memory usage: 590 KiB
  network usage: 0 B (0 messages)
  regions: us-east1
  sql cpu time: 511µs
  estimated RUs consumed: 0
  isolation level: serializable
  priority: normal
  quality of service: regular

  • limit
  │ count: 100
  │
  └── • hash join
      │ sql nodes: n1
      │ regions: us-east1
      │ actual row count: 100
      │ estimated max memory allocated: 320 KiB
      │ estimated max sql temp disk usage: 0 B
      │ sql cpu time: 214µs
      │ estimated row count: 56
      │ equality: (vehicle_id, city) = (id, city)
      │ right cols are key
      │
      ├── • scan
      │     sql nodes: n1
      │     kv nodes: n1
      │     regions: us-east1
      │     actual row count: 500
      │     KV time: 3ms
      │     KV contention time: 0µs
      │     KV rows decoded: 500
      │     KV bytes read: 87 KiB
      │     KV gRPC calls: 1
      │     estimated max memory allocated: 250 KiB
      │     sql cpu time: 264µs
      │     estimated row count: 500 (100% of the table; stats collected 4 days ago)
      │     table: rides@rides_pkey
      │     spans: FULL SCAN
      │
      └── • scan
            sql nodes: n1
            kv nodes: n1
            regions: us-east1
            actual row count: 15
            KV time: 3ms
            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: 32µs
            estimated row count: 15 (100% of the table; stats collected 4 days ago)
            table: vehicles@vehicles_pkey
            spans: FULL SCAN
(61 rows)

View routine body plans

If the statement invokes a routine that is not , the output includes a routine body plan for the routine. Create a VOLATILE function that returns the number of rides taken by a user:

CREATE FUNCTION user_rides(rider UUID, rider_city STRING) RETURNS INT VOLATILE LANGUAGE SQL AS $$ SELECT count(*)::INT FROM rides WHERE rider_id = rider AND city = rider_city $$;

The following EXPLAIN ANALYZE statement invokes the function once for each row scanned in users. The routine section below the main statement plan shows the plan of the function body statement, with invocations: 100 indicating the number of times the function was invoked:

EXPLAIN ANALYZE SELECT name, user_rides(id, city) FROM users WHERE city = 'new york' LIMIT 100;

                                                                 info
--------------------------------------------------------------------------------------------------------------------------------------
  planning time: 1ms
  execution time: 64ms
  distribution: local
  plan type: custom
  rows decoded from KV: 100 (11 KiB, 1 gRPC calls)
  regions: us-east1

  • root
  │
  ├── • render
  │   │ execution time: 61ms
  │   │ sql cpu time: 27ms
  │   │ actual row count: 100
  │   │
  │   └── • scan
  │         sql nodes: n1
  │         kv nodes: n2
  │         regions: us-east1
  │         KV time: 2ms
  │         KV rows decoded: 100
  │         sql cpu time: 101µs
  │         actual row count: 100
  │         estimated row count: 100 (0.80% of the table; stats collected 20 seconds ago)
  │         table: users@users_pkey
  │         spans: [/'new york' - /'new york']
  │         limit: 100
  │
  └── • routine
      │ name: user_rides
      │ invocations: 100
      │
      └── • body stmt
          │ sql: SELECT count(*)::INT8 FROM movr.public.rides WHERE (rider_id = rider) AND (city = rider_city)
          │
          └── • group (scalar)
              │ sql nodes: n1
              │ regions: us-east1
              │ execution time: 4µs
              │ sql cpu time: 4µs
              │ actual row count: 1
              │ estimated row count: 1
              │
              └── • scan
                    sql nodes: n1
                    kv nodes: n3
                    regions: us-east1
                    KV time: 3ms
                    KV rows decoded: 25
                    sql cpu time: 109µs
                    actual row count: 9
                    estimated row count: 12 (<0.01% of the table; stats collected 20 seconds ago)
                    table: rides@rides_auto_index_fk_city_ref_users
                    spans: [/'new york'/'00000000-0000-4000-8000-000000000000' - /'new york'/'00000000-0000-4000-8000-000000000000']
(53 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: 60µs
  execution time: 4ms
  distribution: full
  vectorized: true
  plan type: generic, reused
  rows decoded from KV: 500 (87 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
  kv cpu time: 944µs
  sql cpu time: 275µs
  estimated RUs consumed: 0
  isolation level: serializable
  priority: normal
  quality of service: regular

  • group (streaming)
  │ columns: (city, avg)
  │ sql nodes: n1
  │ regions: us-east1
  │ actual row count: 9
  │ vectorized batch count: 1
  │ sql cpu time: 74µs
  │ estimated row count: 9
  │ aggregate 0: avg(revenue)
  │ group by: city
  │ ordered: +city
  │
  └── • scan
        columns: (city, revenue)
        ordering: +city
        sql nodes: n1
        kv 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: 87 KiB
        KV gRPC calls: 1
        estimated max memory allocated: 130 KiB
        sql cpu time: 201µ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 4 days ago)
        table: rides@rides_pkey
        spans: FULL SCAN
(49 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: 580µs
  execution time: 4ms
  distribution: full
  vectorized: true
  plan type: custom
  rows decoded from KV: 500 (87 KiB, 1 gRPC calls)
  cumulative time spent in KV: 4ms
  maximum memory usage: 240 KiB
  network usage: 0 B (0 messages)
  regions: us-east1
  kv cpu time: 1ms
  sql cpu time: 300µs
  estimated RUs consumed: 0
  isolation level: serializable
  priority: normal
  quality of service: regular

  • group (streaming)
  │ sql nodes: n1
  │ regions: us-east1
  │ actual row count: 9
  │ sql cpu time: 78µs
  │ estimated row count: 9
  │ group by: city
  │ ordered: +city
  │
  └── • scan
        sql nodes: n1
        kv nodes: n1
        regions: us-east1
        actual row count: 500
        KV time: 4ms
        KV contention time: 0µs
        KV rows decoded: 500
        KV bytes read: 87 KiB
        KV gRPC calls: 1
        estimated max memory allocated: 130 KiB
        sql cpu time: 222µs
        estimated row count: 500 (100% of the table; stats collected 4 days ago)
        table: rides@rides_pkey
        spans: FULL SCAN

  Diagram: https://cockroachdb.github.io/distsqlplan/decode.html#eJyUU9FO6z...
(42 rows)

To view the DistSQL plan diagram, open the URL following Diagram. For an example, see .

`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 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: 390µs
  execution time: 20ms
  distribution: full
  vectorized: true
  plan type: custom
  rows decoded from KV: 500 (87 KiB, 1 gRPC calls)
  cumulative time spent in KV: 18ms
  maximum memory usage: 290 KiB
  network usage: 0 B (0 messages)
  regions: us-east1
  kv cpu time: 2ms
  sql cpu time: 2ms
  estimated RUs consumed: 0
  isolation level: serializable
  priority: normal
  quality of service: regular

  • sort
  │ sql nodes: n1
  │ regions: us-east1
  │ actual row count: 58
  │ estimated max memory allocated: 40 KiB
  │ estimated max sql temp disk usage: 0 B
  │ sql cpu time: 827µs
  │ estimated row count: 50
  │ order: +revenue
  │
  └── • filter
      │ sql nodes: n1
      │ regions: us-east1
      │ actual row count: 58
      │ sql cpu time: 373µs
      │ estimated row count: 50
      │ filter: revenue > ‹×›
      │
      └── • scan
            sql nodes: n1
            kv nodes: n1
            regions: us-east1
            actual row count: 500
            KV time: 18ms
            KV contention time: 0µs
            KV rows decoded: 500
            KV bytes read: 87 KiB
            KV gRPC calls: 1
            estimated max memory allocated: 250 KiB
            sql cpu time: 631µs
            estimated row count: 500 (100% of the table; stats collected 4 days ago)
            table: rides@rides_pkey
            spans: FULL SCAN
(49 rows)

In the preceding output, the revenue comparison value is redacted as ‹×›.

Statements

Syntax

Data Types

Spatial Data

Constraints

Aliases

Synopsis

Parameters

Required privileges

Success responses

Global properties

Statement plan tree properties

Routine body plans

`PLAN` option

`PLAN` suboptions

`DISTSQL` option

DistSQL plan diagram

`DEBUG` option

`REDACT` option

Examples

`EXPLAIN ANALYZE`

View routine body plans

`EXPLAIN ANALYZE (VERBOSE)`

`EXPLAIN ANALYZE (DISTSQL)`

`EXPLAIN ANALYZE (DEBUG)`

`EXPLAIN ANALYZE (REDACT)`

See also

​Aliases

​Synopsis

​Parameters

​Required privileges

​Success responses

​Global properties

​Statement plan tree properties

​Routine body plans

​PLAN option

​PLAN suboptions

​DISTSQL option

​DistSQL plan diagram

​DEBUG option

​REDACT option

​Examples

​EXPLAIN ANALYZE

​View routine body plans

​EXPLAIN ANALYZE (VERBOSE)

​EXPLAIN ANALYZE (DISTSQL)

​EXPLAIN ANALYZE (DEBUG)

​EXPLAIN ANALYZE (REDACT)

​See also

Aliases

Synopsis

Parameters

Required privileges

Success responses

Global properties

Statement plan tree properties

Routine body plans

`PLAN` option

`PLAN` suboptions

`DISTSQL` option

DistSQL plan diagram

`DEBUG` option

`REDACT` option

Examples

`EXPLAIN ANALYZE`

View routine body plans

`EXPLAIN ANALYZE (VERBOSE)`

`EXPLAIN ANALYZE (DISTSQL)`

`EXPLAIN ANALYZE (DEBUG)`

`EXPLAIN ANALYZE (REDACT)`

See also