# EXPLAIN

The `EXPLAIN` statement returns CockroachDB's query plan for an explainable statement. You can then use this information to optimize the query.

Tip:

To actually execute a statement and return a physical query plan with execution statistics, use `EXPLAIN ANALYZE`.

## Query optimization

Using `EXPLAIN`'s output, you can optimize your queries by taking the following points into consideration:

- Queries with fewer levels execute more quickly. Restructuring queries to require fewer levels of processing will generally improve performance.

- Avoid scanning an entire table, which is the slowest way to access data. You can avoid this by creating indexes that contain at least one of the columns that the query is filtering in its `WHERE` clause.

- By default, the vectorized execution engine is enabled for all supported operations and data types. If you are querying a table with a small number of rows, it might be more efficient to use row-oriented execution. The `vectorize_row_count_threshold` cluster setting specifies the minimum number of rows required to use the vectorized engine to execute a query plan.

You can find out if your queries are performing entire table scans by using `EXPLAIN` to see which:

- Indexes the query uses; shown as the Description value of rows with the Field value of `table`

- Key values in the index are being scanned; shown as the Description value of rows with the Field value of `spans`

For more information, see Find the Indexes and Key Ranges a Query Uses.

## Synopsis

## Required privileges

The user requires the appropriate privileges for the statement being explained.

## Parameters

Parameter | Description
--- | ---
`VERBOSE` | Show as much information as possible about the query plan.
`TYPES` | Include the intermediate data types CockroachDB chooses to evaluate intermediate SQL expressions.
`OPT` | Display the query plan tree generated by the cost-based optimizer. To include cost details used by the optimizer in planning the query, use `OPT, VERBOSE`. To include cost and type details, use `OPT, TYPES`. To include all details used by the optimizer, including statistics, use `OPT, ENV`.
`VEC` | Show detailed information about the vectorized execution plan for a query. If the table queried includes unsupported data types, an unhandled data type error is returned.
`preparable_stmt` | The statement you want details about. All preparable statements are explainable.
`DISTSQL` | Generate a URL to a distributed SQL physical query plan tree. The generated physical query 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 query plan diagram. The query plan is, therefore, not logged by a server external to the CockroachDB cluster and not exposed to the public internet.

Warning:

`EXPLAIN` also includes other modes besides query plans that are useful only to CockroachDB developers, which are not documented here.

## Success responses

Successful `EXPLAIN` statements return tables with the following columns:

Column | Description
--- | ---
Tree | A tree representation of the hierarchy of the query plan.
Field | The name of a property for the query plan. The `distributed` and `vectorized` properties apply to the entire query plan. All other properties apply to the query plan node in the Tree column.
Description | Additional information about the parameter in Field.
Columns | The columns provided to the processes at lower levels of the hierarchy. Included in `TYPES` and `VERBOSE` output.
Ordering | The order in which results are presented to the processes at each level of the hierarchy, as well as other properties of the result set at each level. Included in `TYPES` and `VERBOSE` output.

## Examples

The following examples use the `startrek` example dataset. To follow along, you can use `cockroach demo startrek` to start a temporary, in-memory cluster with the `startrek` dataset preloaded.

### Default query plans

By default, `EXPLAIN` includes the least detail about the query plan but can be useful to find out which indexes and index key ranges are used by a query. For example:

    > EXPLAIN SELECT * FROM episodes WHERE season > 3 ORDER BY season ASC;

        tree    |    field    |   description
    ------------+-------------+-------------------
                | distributed | true
                | vectorized  | false
      sort      |             |
       │        | order       | +season
       └── scan |             |
                | table       | episodes@primary
                | spans       | FULL SCAN
                | filter      | season > 3
    (8 rows)

The `tree` column of the output shows the tree structure of the query plan, in this case a `sort` and then a `scan`.

The `field` and `description` columns describe a set of properties, some global to the query, and some specific to an operation listed in the `tree` column (in this case, `sort` or `scan`):

- `distributed`:`true`
  The query plan will be distributed to multiple nodes on the cluster.
- `vectorized`:`false`
  The plan will be executed with the row-oriented execution engine, and not the vectorized engine.
- `order`:`+season`
  The sort will be ordered ascending on the `season` column.
- `table`:`episodes@primary`
  The table is scanned on the `primary` index.
- `spans`:`FULL SCAN`
  The table is scanned on all key ranges of the `primary` index (i.e., a full table scan). For more information on indexes and key ranges, see the example below.
- `filter`: `season > 3`
  The scan filters on the `season` column.

If you run `EXPLAIN` on a join query, the output will display which type of join will be executed. For example, the following `EXPLAIN` output shows that the query will perform a hash join:

    > EXPLAIN SELECT * FROM quotes AS q
    JOIN episodes AS e ON q.episode = e.id;

        tree    |       field        |   description
    ------------+--------------------+-------------------
                | distributed        | true
                | vectorized         | false
      hash-join |                    |
       │        | type               | inner
       │        | equality           | (episode) = (id)
       │        | right cols are key |
       ├── scan |                    |
       │        | table              | quotes@primary
       │        | spans              | FULL SCAN
       └── scan |                    |
                | table              | episodes@primary
                | spans              | FULL SCAN
    (12 rows)

And the following output shows that the query will perform a simple cross join:

    > EXPLAIN SELECT * FROM quotes AS q
    JOIN episodes AS e ON q.episode = '2';

              tree         |    field    |        description
    -----------------------+-------------+----------------------------
                           | distributed | true
                           | vectorized  | false
      render               |             |
       └── cross-join      |             |
            │              | type        | cross
            ├── scan       |             |
            │              | table       | episodes@primary
            │              | spans       | FULL SCAN
            └── index-join |             |
                 │         | table       | quotes@primary
                 │         | key columns | rowid
                 └── scan  |             |
                           | table       | quotes@quotes_episode_idx
                           | spans       | /2-/3
    (14 rows)

### `VERBOSE` option

The `VERBOSE` option:

- Includes SQL expressions that are involved in each processing stage, providing more granular detail about which portion of your query is represented at each level.
- Includes detail about which columns are being used by each level, as well as properties of the result set on that level.

    > EXPLAIN (VERBOSE) SELECT * FROM quotes AS q
    JOIN episodes AS e ON q.episode = e.id
    WHERE e.season = '1'
    ORDER BY e.stardate ASC;

The output of `EXPLAIN` also shows whether `equality cols are key` for lookup joins, which means that the lookup columns form a key in the target table such that each lookup has at most one result.

           tree      |       field        |   description    |                                 columns                                  | ordering
    -----------------+--------------------+------------------+--------------------------------------------------------------------------+------------
                     | distributed        | true             |                                                                          |
                     | vectorized         | false            |                                                                          |
      sort           |                    |                  | (quote, characters, stardate, episode, id, season, num, title, stardate) | +stardate
       │             | order              | +stardate        |                                                                          |
       └── hash-join |                    |                  | (quote, characters, stardate, episode, id, season, num, title, stardate) |
            │        | type               | inner            |                                                                          |
            │        | equality           | (episode) = (id) |                                                                          |
            │        | right cols are key |                  |                                                                          |
            ├── scan |                    |                  | (quote, characters, stardate, episode)                                   |
            │        | table              | quotes@primary   |                                                                          |
            │        | spans              | FULL SCAN        |                                                                          |
            └── scan |                    |                  | (id, season, num, title, stardate)                                       |
                     | table              | episodes@primary |                                                                          |
                     | spans              | FULL SCAN        |                                                                          |
                     | filter             | season = 1       |                                                                          |
    (15 rows)

### `TYPES` option

The `TYPES` mode includes the types of the values used in the query plan. It also includes the SQL expressions that were involved in each processing stage, and includes the columns used by each level.

    > EXPLAIN (TYPES) SELECT * FROM episodes WHERE season > 3 ORDER BY season ASC;

        tree    |    field    |           description            |                            columns                            | ordering
    ------------+-------------+----------------------------------+---------------------------------------------------------------+-----------
                | distributed | true                             |                                                               |
                | vectorized  | false                            |                                                               |
      sort      |             |                                  | (id int, season int, num int, title string, stardate decimal) | +season
       │        | order       | +season                          |                                                               |
       └── scan |             |                                  | (id int, season int, num int, title string, stardate decimal) |
                | table       | episodes@primary                 |                                                               |
                | spans       | FULL SCAN                        |                                                               |
                | filter      | ((season)[int] > (3)[int])[bool] |                                                               |
    (8 rows)

### `OPT` option

The `OPT` option displays the query plan tree generated by the cost-based optimizer. For example:

    > EXPLAIN (OPT) SELECT * FROM episodes WHERE season > 3 ORDER BY season ASC;

                text
    -----------------------------
      sort
       └── select
            ├── scan episodes
            └── filters
                 └── season > 3
    (5 rows)

To include cost details used by the optimizer in planning the query, use `OPT, VERBOSE`:

    > EXPLAIN (OPT, VERBOSE) SELECT * FROM episodes WHERE season > 3 ORDER BY season ASC;

                                                                                                                                         text

      sort
       ├── columns: id:1 season:2 num:3 title:4 stardate:5
       ├── stats: [rows=26.3333333, distinct(2)=1, null(2)=0]
       ├── cost: 98.6419109
       ├── key: (1)
       ├── fd: (1)-->(2-5)
       ├── ordering: +2
       ├── prune: (1,3-5)
       ├── interesting orderings: (+1)
       └── select
            ├── columns: id:1 season:2 num:3 title:4 stardate:5
            ├── stats: [rows=26.3333333, distinct(2)=1, null(2)=0]
            ├── cost: 95.62
            ├── key: (1)
            ├── fd: (1)-->(2-5)
            ├── prune: (1,3-5)
            ├── interesting orderings: (+1)
            ├── scan episodes
            │    ├── columns: id:1 season:2 num:3 title:4 stardate:5
            │    ├── stats: [rows=79, distinct(1)=79, null(1)=0, distinct(2)=3, null(2)=0]
            │    │   histogram(1)=  0  1  0  1  0  1  0  1  0  1  0  1  0  1  0  1  0  1  0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1
            │    │                <--- 1 --- 2 --- 3 --- 4 --- 5 --- 6 --- 7 --- 8 --- 9 --- 10 --- 11 --- 12 --- 13 --- 14 --- 15 --- 16 --- 17 --- 18 --- 19 --- 20 --- 21 --- 22 --- 23 --- 24 --- 25 --- 26 --- 27 --- 28 --- 29 --- 30 --- 31 --- 32 --- 33 --- 34 --- 35 --- 36 --- 37 --- 38 --- 39 --- 40 --- 41 --- 42 --- 43 --- 44 --- 45 --- 46 --- 47 --- 48 --- 49 --- 50 --- 51 --- 52 --- 53 --- 54 --- 55 --- 56 --- 57 --- 58 --- 59 --- 60 --- 61 --- 62 --- 63 --- 64 --- 65 --- 66 --- 67 --- 68 --- 69 --- 70 --- 71 --- 72 --- 73 --- 74 --- 75 --- 76 --- 77 --- 78 --- 79
            │    ├── cost: 94.82
            │    ├── key: (1)
            │    ├── fd: (1)-->(2-5)
            │    ├── prune: (1-5)
            │    └── interesting orderings: (+1)
            └── filters
                 └── season:2 > 3 [outer=(2), constraints=(/2: [/4 - ]; tight)]
    (29 rows)

To include cost and type details, use `OPT, TYPES`:

    > EXPLAIN (OPT, TYPES) SELECT * FROM episodes WHERE season > 3 ORDER BY season ASC;

                                                                                                                                         text

      sort
       ├── columns: id:1(int!null) season:2(int!null) num:3(int) title:4(string) stardate:5(decimal)
       ├── stats: [rows=26.3333333, distinct(2)=1, null(2)=0]
       ├── cost: 98.6419109
       ├── key: (1)
       ├── fd: (1)-->(2-5)
       ├── ordering: +2
       ├── prune: (1,3-5)
       ├── interesting orderings: (+1)
       └── select
            ├── columns: id:1(int!null) season:2(int!null) num:3(int) title:4(string) stardate:5(decimal)
            ├── stats: [rows=26.3333333, distinct(2)=1, null(2)=0]
            ├── cost: 95.62
            ├── key: (1)
            ├── fd: (1)-->(2-5)
            ├── prune: (1,3-5)
            ├── interesting orderings: (+1)
            ├── scan episodes
            │    ├── columns: id:1(int!null) season:2(int) num:3(int) title:4(string) stardate:5(decimal)
            │    ├── stats: [rows=79, distinct(1)=79, null(1)=0, distinct(2)=3, null(2)=0]
            │    │   histogram(1)=  0  1  0  1  0  1  0  1  0  1  0  1  0  1  0  1  0  1  0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1   0  1
            │    │                <--- 1 --- 2 --- 3 --- 4 --- 5 --- 6 --- 7 --- 8 --- 9 --- 10 --- 11 --- 12 --- 13 --- 14 --- 15 --- 16 --- 17 --- 18 --- 19 --- 20 --- 21 --- 22 --- 23 --- 24 --- 25 --- 26 --- 27 --- 28 --- 29 --- 30 --- 31 --- 32 --- 33 --- 34 --- 35 --- 36 --- 37 --- 38 --- 39 --- 40 --- 41 --- 42 --- 43 --- 44 --- 45 --- 46 --- 47 --- 48 --- 49 --- 50 --- 51 --- 52 --- 53 --- 54 --- 55 --- 56 --- 57 --- 58 --- 59 --- 60 --- 61 --- 62 --- 63 --- 64 --- 65 --- 66 --- 67 --- 68 --- 69 --- 70 --- 71 --- 72 --- 73 --- 74 --- 75 --- 76 --- 77 --- 78 --- 79
            │    ├── cost: 94.82
            │    ├── key: (1)
            │    ├── fd: (1)-->(2-5)
            │    ├── prune: (1-5)
            │    └── interesting orderings: (+1)
            └── filters
                 └── gt [type=bool, outer=(2), constraints=(/2: [/4 - ]; tight)]
                      ├── variable: season:2 [type=int]
                      └── const: 3 [type=int]
    (31 rows)

To include all details used by the optimizer, including statistics, use `OPT, ENV`.

    > EXPLAIN (OPT, ENV) SELECT * FROM episodes WHERE season > 3 ORDER BY season ASC;

                  text

      https://cockroachdb.github.io/text/decode.html#
    (1 row)

The output of `EXPLAIN (OPT,ENV)` is now a URL with the data encoded in the fragment portion. Opening the URL shows a page with the decoded data. This change makes it easier to share debugging information across different systems without encountering formatting issues.

Note that the data is processed in the local browser session and is never sent out over the network. Keep in mind that if you are using any browser extensions, they may be able to access the data locally.

When you visit the URL above you should see the following output in your browser.

    -- Version: CockroachDB CCL v20.1.0 (x86_64-apple-darwin19.3.0, built 2020/03/31 16:16:33, go1.13.4)

    -- reorder_joins_limit has the default value: 4
    -- enable_zigzag_join has the default value: on
    -- optimizer_foreign_keys has the default value: on

    CREATE TABLE episodes (
        id INT8 NOT NULL,
        season INT8 NULL,
        num INT8 NULL,
        title STRING NULL,
        stardate DECIMAL NULL,
        CONSTRAINT "primary" PRIMARY KEY (id ASC),
        FAMILY "primary" (id, season, num, title, stardate)
    );

    ALTER TABLE startrek.public.episodes INJECT STATISTICS '[
        {
            "columns": [
                "id"
            ],
            "created_at": "2020-04-01 17:46:35.112348+00:00",
            "distinct_count": 79,
            "histo_col_type": "INT8",
            "name": "__auto__",
            "null_count": 0,
            "row_count": 79
        },
        {
            "columns": [
                "season"
            ],
            "created_at": "2020-04-01 17:46:35.112348+00:00",
            "distinct_count": 3,
            "histo_col_type": "",
            "name": "__auto__",
            "null_count": 0,
            "row_count": 79
        },
        {
            "columns": [
                "num"
            ],
            "created_at": "2020-04-01 17:46:35.112348+00:00",
            "distinct_count": 29,
            "histo_col_type": "",
            "name": "__auto__",
            "null_count": 0,
            "row_count": 79
        },
        {
            "columns": [
                "title"
            ],
            "created_at": "2020-04-01 17:46:35.112348+00:00",
            "distinct_count": 79,
            "histo_col_type": "",
            "name": "__auto__",
            "null_count": 0,
            "row_count": 79
        },
        {
            "columns": [
                "stardate"
            ],
            "created_at": "2020-04-01 17:46:35.112348+00:00",
            "distinct_count": 76,
            "histo_col_type": "",
            "name": "__auto__",
            "null_count": 4,
            "row_count": 79
        }
    ]';

    EXPLAIN (OPT, ENV) SELECT * FROM episodes WHERE season > 3 ORDER BY season ASC;
    ----
    sort
     └── select
          ├── scan episodes
          └── filters
               └── season > 3

### `VEC` option

The `VEC` option shows details about the vectorized execution plan for the query.

    > EXPLAIN (VEC) SELECT * FROM episodes WHERE season > 3;

                     text
    ---------------------------------------
      │
      └ Node 1
        └ *colexec.selGTInt64Int64ConstOp
          └ *colexec.colBatchScan
    (4 rows)

The output shows the different internal functions that will be used to process each batch of column-oriented data.

### `DISTSQL` option

The `DISTSQL` option generates a URL for a physical query plan that provides high level information about how a query will be executed. For details about reading the physical query plan, see DistSQL Plan Viewer. For more information about distributed SQL queries, see the DistSQL section of our SQL Layer Architecture docs.

Note:

The generated physical query 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 query plan diagram. The query plan is, therefore, not logged by a server external to the CockroachDB cluster and not exposed to the public internet.

For example, the following `EXPLAIN(DISTSQL)` statement generates a physical plan for a simple query against the TPC-H database loaded to a 3-node CockroachDB cluster:

    > EXPLAIN (DISTSQL) SELECT l_shipmode, AVG(l_extendedprice) FROM lineitem GROUP BY l_shipmode;

     automatic |                      url
    -----------+----------------------------------------------
       true    | https://cockroachdb.github.io/distsqlplan...

To view the DistSQL Plan Viewer, point your browser to the URL provided:

New in v20.1: To include the data types of the input columns in the physical plan, use `EXPLAIN(DISTSQL, TYPES)`:

    > EXPLAIN (DISTSQL, TYPES) SELECT l_shipmode, AVG(l_extendedprice) FROM lineitem GROUP BY l_shipmode;

     automatic |                      url
    -----------+----------------------------------------------
       true    | https://cockroachdb.github.io/distsqlplan...

To view the DistSQL Plan Viewer, point your browser to the URL provided:

### Find the indexes and key ranges a query uses

You can use `EXPLAIN` to understand which indexes and key ranges queries use, which can help you ensure a query isn't performing a full table scan.

    > CREATE TABLE kv (k INT PRIMARY KEY, v INT);

Because column `v` is not indexed, queries filtering on it alone scan the entire table:

    > EXPLAIN SELECT * FROM kv WHERE v BETWEEN 4 AND 5;

      tree |    field    |      description
    -------+-------------+------------------------
           | distributed | true
           | vectorized  | false
      scan |             |
           | table       | kv@primary
           | spans       | FULL SCAN
           | filter      | (v >= 4) AND (v <= 5)
    (6 rows)

If there were an index on `v`, CockroachDB would be able to avoid scanning the entire table:

    > CREATE INDEX v ON kv (v);

    > EXPLAIN SELECT * FROM kv WHERE v BETWEEN 4 AND 5;

      tree |    field    | description
    -------+-------------+--------------
           | distributed | false
           | vectorized  | false
      scan |             |
           | table       | kv@v
           | spans       | /4-/6
    (5 rows)

Now, only part of the index `v` is getting scanned, specifically the key range starting at (and including) 4 and stopping before 6. Also note that this query plan is not distributed across nodes on the cluster.

### Find out if a statement is using `SELECT FOR UPDATE` locking

New in v20.1: CockroachDB has support for ordering transactions by controlling concurrent access to one or more rows of a table using locks. This "`SELECT FOR UPDATE` locking" can result in improved performance for contended operations. It applies to the following statements:

- `SELECT FOR UPDATE`
- `UPDATE`

To see whether a SQL query using one of these statements is using this feature, check the output of `EXPLAIN` for a `locking strength` field as shown below. If the `locking strength` field does not appear, then the statement is not using this feature.

    > CREATE TABLE IF NOT EXISTS kv (k INT PRIMARY KEY, v INT);
    UPSERT INTO kv (k, v) VALUES (1, 5), (2, 10), (3, 15);

    > EXPLAIN UPDATE kv SET v = 100 WHERE k = 1;

             tree         |      field       | description
    ----------------------+------------------+--------------
                          | distributed      | false
                          | vectorized       | false
      count               |                  |
       └── update         |                  |
            │             | table            | kv
            │             | set              | v
            │             | strategy         | updater
            │             | auto commit      |
            └── render    |                  |
                 └── scan |                  |
                          | table            | kv@primary
                          | spans            | /1-/1/#
                          | locking strength | for update
    (13 rows)

By default, `SELECT FOR UPDATE` locking is enabled for the initial row scan of `UPDATE` statements. To disable it, toggle the `enable_implicit_select_for_update` session setting.

## See also

- `ALTER TABLE`
- `ALTER SEQUENCE`
- `BACKUP`
- `CANCEL JOB`
- `CREATE DATABASE`
- `DROP DATABASE`
- `EXECUTE`
- `EXPLAIN ANALYZE`
- `IMPORT`
- Indexes
- `INSERT`
- `PAUSE JOB`
- `RESET`
- `RESTORE`
- `RESUME JOB`
- `SELECT`
- Selection Queries
- `SET`
- `SET CLUSTER SETTING`
- `SHOW COLUMNS`
- `UPDATE`
- `UPSERT`