Skip to content

EXPLAIN

The primary effect of this command is to display the execution plan generated by the MatrixOne scheduler for the supplied statement.

EXPLAIN - Shows the execution plan for a statement.

Syntax structure

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

Note

The parenthesized option syntax is supported for ANALYZE and VERBOSE options (e.g., EXPLAIN (ANALYZE TRUE), EXPLAIN (ANALYZE FALSE), EXPLAIN (VERBOSE TRUE), EXPLAIN (VERBOSE FALSE)), but is not yet supported for the FORMAT option (e.g., EXPLAIN (FORMAT=TEXT)). Bare keyword forms (EXPLAIN, EXPLAIN ANALYZE, EXPLAIN VERBOSE) are also available.

Syntax Description

The primary effect of this command is to display the execution plan generated by the MatrixOne scheduler for the supplied statement. The execution plan shows how to scan the tables referenced by statements through normal sequential scans, index scans, etc. If multiple tables are referenced, what join algorithm will be used to bring together the required rows in each input table.

The most critical part of the display is estimating the cost of statement execution, that is, the scheduler will estimate the time it will take to run the statement (measured in either cost unit, but typically obtained from disk pages). Actually two numbers are shown here: the startup cost before returning the first row, and the total cost of returning all rows. For most queries, the total cost is the most important, but in subqueries in EXISTS, the scheduler chooses the smallest start-up cost over the smallest total cost (because the performer stops after getting a row). In addition, if you limit the number of rows returned with a LIMIT clause, the scheduler will interpolate the endpoint costs appropriately to estimate which plan is really cheapest.

The ANALYZE clause syntax option adds actual runtime statistics to the display for statement actual execution, not just scheduled execution, including the total run time (in milliseconds) spent in each scheduled node and the total number of rows actually returned. This helps to understand whether planners' expectations are close to reality.

Parameter interpretation

  • ANALYZE:

Executes the command and displays actual runtime and other statistics. This parameter defaults to FALSE.

  • VERBOSE:

VERBOSE is used to display additional information about the plan. Specifically, include a list of output columns, schema-qualified tables, and function names for each node in the plan tree, always tag variables in expressions with range table aliases, and always print the name of each trigger that displays statistics. This parameter defaults to FALSE.

  • FORMAT:

FORMAT=TEXT output is the only supported format. The parenthesized option syntax (e.g., EXPLAIN (FORMAT=TEXT)) is not yet implemented; only bare keyword forms are available.

  • BOOLEAN:

The ANALYZE and VERBOSE options accept an optional boolean value (TRUE or FALSE). The parenthesized option syntax is supported for these options (e.g., EXPLAIN (ANALYZE TRUE), EXPLAIN (VERBOSE FALSE)). Bare keyword forms (EXPLAIN ANALYZE, EXPLAIN VERBOSE) are also available and default to TRUE.

  • STATEMENT

MatrixOne supports any SELECT, INSERT, UPDATE, DELETE statement execution plan.