> ## Documentation Index
> Fetch the complete documentation index at: https://docs.risingwave.com/llms.txt
> Use this file to discover all available pages before exploring further.

# EXPLAIN

> Use the `EXPLAIN` command to show the execution plan of a statement, or use `DESCRIBE FRAGMENTS <job_name>` to show execution fragments of an existing job.

## Syntax

```sql theme={null}
EXPLAIN [ ( option [ , ... ] ) ] statement;
```

## Parameters

| Parameter            | Description                                                                                                                                                                                             |
| :------------------- | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| *statement*          | A statement that is executable in RisingWave.                                                                                                                                                           |
| **EXPLAIN** *option* | See the table below. Since v2.7.0, `EXPLAIN` option names are parsed as identifiers, not SQL keywords. This means they are case-insensitive and do not need to be escaped or written as reserved words. |

#### `EXPLAIN` options

| Option      | Supported values                     | Description                                                                                                                                                                                                                                                                                                                                                        |
| :---------- | :----------------------------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| **VERBOSE** | `[ TRUE \| FALSE ]`                  | Show additional information regarding the execution plan such as the table catalog of the state table and the schema of each operator.                                                                                                                                                                                                                             |
| **TRACE**   | `[ TRUE \| FALSE ]`                  | Show the trace of each optimization stage, not only the final plan.                                                                                                                                                                                                                                                                                                |
| **TYPE**    | `[ PHYSICAL \| LOGICAL \| DISTSQL ]` | Show the execution plan of a specific phase.<ul><li>PHYSICAL — Show the batch plan or stream plan.</li><li>LOGICAL — Show the optimized logical plan.</li><li>DISTSQL — Show the distributed query plan for batch or stream.</li></ul>                                                                                                                             |
| **FORMAT**  | `[ TEXT \| JSON \| XML \| YAML ]`    | <ul><li>FORMAT JSON - Show the `logical` and `physical` plans in JSON format while ignoring `trace` and `distsql` options.</li><li>FORMAT TEXT - Match the current output format of `EXPLAIN`.</li><li>FORMAT XML - Show the `logical` and `physical` plans in XML format.</li><li>FORMAT YAML - Show the `logical` and `physical` plans in YAML format.</li></ul> |

<Note>
  The boolean parameter `[ TRUE | FALSE ]` specifies whether the specified option should be enabled or disabled. Use `TRUE` to enable the option, and `FALSE` to disable it. It defaults to `TRUE` if the parameter is not specified.
</Note>

## Examples

The following statement shows the execution plan of a `SELECT` statement.

```sql theme={null}
EXPLAIN SELECT
    o_orderpriority,
    count(*) AS order_count
FROM
    orders
WHERE
    o_orderdate >= date '1997-07-01'
    and o_orderdate < date '1997-07-01' + interval '3' month
    and exists (
        SELECT
            *
        FROM
            lineitem
        WHERE
            l_orderkey = o_orderkey
            and l_commitdate < l_receiptdate
    )
GROUP BY
    o_orderpriority
ORDER BY
    o_orderpriority;
```

The execution plan looks like this:

```js theme={null}
 BatchExchange { order: [orders.o_orderpriority ASC], dist: Single }
   BatchSort { order: [orders.o_orderpriority ASC] }
     BatchHashAgg { group_key: [orders.o_orderpriority], aggs: [count] }
       BatchExchange { order: [], dist: HashShard(orders.o_orderpriority) }
         BatchHashJoin { type: LeftSemi, predicate: orders.o_orderkey = lineitem.l_orderkey }
           BatchExchange { order: [], dist: HashShard(orders.o_orderkey) }
             BatchProject { exprs: [orders.o_orderkey, orders.o_orderpriority] }
               BatchFilter { predicate: (orders.o_orderdate >= '1997-07-01':Varchar::Date) AND (orders.o_orderdate < ('1997-07-01':Varchar::Date + '3 mons 00:00:00':Interval)) }
                 BatchScan { table: orders, columns: [o_orderkey, o_orderpriority, o_orderdate] }
           BatchExchange { order: [], dist: HashShard(lineitem.l_orderkey) }
             BatchProject { exprs: [lineitem.l_orderkey] }
               BatchFilter { predicate: (lineitem.l_commitdate < lineitem.l_receiptdate) }
                 BatchScan { table: lineitem, columns: [l_orderkey, l_commitdate, l_receiptdate] }
(13 rows)
```

The following statement shows the execution plan of a `CREATE MATERIALIZED VIEW` statement.

```sql theme={null}
EXPLAIN CREATE MATERIALIZED VIEW nexmark_q3 AS
     SELECT P.name, P.city, P.state, A.id
     FROM auction AS A INNER JOIN person AS P on A.seller = P.id
     WHERE A.category = 10 and (P.state = 'OR' OR P.state = 'ID' OR P.state = 'CA');
```

The execution plan of the statement above looks like this:

```sql theme={null}
                      QUERY PLAN
-----------------------------------------------------------
 StreamMaterialize { columns: [name, city, state, id, _row_id(hidden), _row_id#1(hidden)], pk_columns: [_row_id, _row_id#1] }
   StreamExchange { dist: HashShard([4, 5]) }
     StreamProject { exprs: [$4, $5, $6, $0, $2, $7] }
       StreamHashJoin { type: Inner, predicate: $1 = $3 }
         StreamProject { exprs: [$0, $1, $3] }
           StreamExchange { dist: HashShard([1]) }
             StreamFilter { predicate: ($2 = 10:Int32) }
               StreamTableScan { table: auction, columns: [id, seller, category, _row_id], pk_indices: [3] }
         StreamExchange { dist: HashShard([0]) }
           StreamFilter { predicate: ((($3 = 'OR':Varchar) OR ($3 = 'ID':Varchar)) OR ($3 = 'CA':Varchar)) }
             StreamTableScan { table: person, columns: [id, name, city, state, _row_id], pk_indices: [4] }
```

To check the execution fragments of an existing job, you can use [`DESCRIBE FRAGMENTS <relation_name>`](/sql/commands/sql-describe#describe-fragments),
which has a similar format like `EXPLAIN (DISTSQL)`, but `DESCRIBE FRAGMENTS` operates on an existing job rather than a new statement being planned.

To check the runtime performance of each operator, use [`EXPLAIN ANALYZE`](/sql/commands/sql-explain-analyze).