Skip to content

EXPLAIN

EXPLAIN - Shows the execution plan for a statement.

Syntax structure

EXPLAIN [ ( option [, ...] ) ] statement

where option can be one of:
    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    (FORMAT=TEXT)

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 is used as the specified output format, the syntax is explain (format xx), only TEXT format is supported for now. Non-text output contains information in the same format as text output and is easily parsed by the program. This parameter defaults to TEXT.

  • BOOLEAN:

BOOLEAN Specifies whether the selected option is on or off. You can write TRUE to enable this option, or FALSE to disable it. This parameter defaults to TRUE.

  • STATEMENT

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