Skip to content

EXPLAIN

EXPLAIN — Shows the execution plan of a statement.

Syntax Structure

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

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

Syntax Description

This command mainly serves to display the execution plan generated by the MatrixOne scheduler for the statements provided. The execution plan shows how to scan a table referenced by a statement through normal sequential scans, index scans, etc. If multiple tables are referenced, what join algorithm will be used to gather the required rows in each input table together.

The most critical part of display is estimating the execution cost of a statement, i.e. the scheduler will estimate the time it takes to run the statement (measured in any unit of cost, but is usually obtained from disk pages). Actually, two numbers are shown here: the startup cost before the first row, and the total cost of returning all rows. Total cost is the most important for most queries, but in subqueries in EXISTS, the planner chooses the smallest startup cost instead of the smallest total cost (because the executor stops after getting a row). Additionally, if you use the LIMIT clause to limit the number of rows returned, the planner will interpolate appropriately between the endpoint costs to estimate which plan is truly the cheapest.

The ANALYZE clause syntax option is for the statement to be actually executed, not just scheduled execution, and then adds the actual runtime statistics to the display, including the total run time spent in milliseconds and the total number of rows actually returned. This helps to understand whether planner expectations are close to reality.

Parameter definition

  • ANALYZE:

Execute the command and display the actual runtime and other statistics. This parameter defaults to FALSE.

  • VERBOSE:

VERBOSE is used to display additional information about the plan. Specifically, including the output column list, schema qualifying table, and function names for each node in the plan tree, always mark the variables in the expression with a range table alias, 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, with the syntax of explain ( format xx ), and only the TEXT format is supported. Non-text output contains the same information as the text output format 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 the option, or write FALSE to disable it. This parameter defaults to TRUE.

  • STATEMENT

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