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

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. PHYSICAL — Show the batch plan or stream plan. LOGICAL — Show the optimized logical plan. DISTSQL — Show the distributed query plan for batch or stream.
FORMAT	[ TEXT | JSON | XML | YAML ]	FORMAT JSON - Show the logical and physical plans in JSON format while ignoring trace and distsql options. FORMAT TEXT - Match the current output format of EXPLAIN. FORMAT XML - Show the logical and physical plans in XML format. FORMAT YAML - Show the logical and physical plans in YAML format.

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.

Examples

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

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:

 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.

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:

                      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>, 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.