EXPLAIN

EXPLAIN — shows the execution plan for a statement

Syntax

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

where statement is any syntactically correct SELECT, INSERT, UPDATE, REPLACE or DELETE statement.

Description

The NuoDB SQL optimizer makes decisions about how to efficiently retrieve, join, group or summarize, and sort the rows that the specified SQL statement operates on. These decisions are stored in the form of a query execution plan which later can be executed multiple times by the query engine. The EXPLAIN command is used to display this query execution plan.

With the information provided in the execution plan, you might choose to modify SQL statements or database structure to improve performance. For example, you might decide to add or remove indexes, or override the indexes that the optimizer chooses by using hints (see Using Optimizer Hints).

The EXPLAIN command is available to all client connections. You can call it from any client including the NuoDB SQL command line tool.

For details about the EXPLAIN command output and how to interpret the information that it provides, including some examples, see Understanding EXPLAIN Command Output.

The EXPLAIN command is not part of the SQL standard. Each database vendor has its own idiosyncratic EXPLAIN command output. The NuoDB execution plan is a tree structure and should be interpreted from the bottom up. The exception to this is the JOIN order, which should be interpreted from the top down.

Parameters

The EXPLAIN command accepts a comma-separated list of explain_option parameters that allows to obtain additional information about the execution plan, or to modify the output of EXPLAIN. Each parameter is a pair option_name parameter. This comma-separated list must be enclosed in parentheses.

ANALYZE { ON | OFF }

Set ANALYZE to ON to cause the EXPLAIN command to evaluate the SQL statement and instruct the execution engine to collect actual statistics for each operator of the execution plan. This statistics includes the number of records or keys output by each operator, and the cumulative execution time of the operator. These statistics are displayed in EXPLAIN format annotated with these counter and time information per plan operator.

The default value is OFF.

See Understanding EXPLAIN Command Output for a detailed description of the additional information provided by this option.

OPT_ESTIMATES { ON | OFF }

Set OPT_ESTIMATES to ON to cause the EXPLAIN command output to include cost estimates for each index, table access, sort, or group operation in the execution plan.

The default setting is ON.

See Understanding EXPLAIN Command Output for a detailed description of the additional information provided by this option.

SCHEMA_PREFIX {ON | OFF}

This option controls whether to prefix table names with their schema. This is useful for statements that reference tables in different schemas.

The default is OFF.

Sample Output

The following shows sample output from the EXPLAIN command. This output is displayed for a very simple SQL SELECT statement with a WHERE clause. See Understanding EXPLAIN Command Output for more examples and a more detailed explanation of the output.

This EXPLAIN command output shows the following:

  • The EXPLAIN command for the SQL SELECT statement.

  • The Project operation, which shows the fully qualified names of each of the columns being retrieved.

  • A Bitmap scan operator which represents a bitmap index scan of the PLAYER_IDX index.

  • The cost estimate values, which display based on the OPT_ESTIMATES option being set to ON. This shows that the bitmap scan will produce 4 keys, and the cost to read those keys is 100.4 cost units.

  • A sequence of Filter operators, which represents a conjunction of SQL statement’s predicates.

SHOW TABLE hockey.hockey;
    Found table HOCKEY in schema HOCKEY
        Fields:
            ID bigint
                Nullable: No
                Generator: HOCKEY$IDENTITY_SEQUENCE    Generated Always
            NUMBER integer
            NAME string
            POSITION string
            TEAM string
        Primary Index: HOCKEY..PRIMARY_KEY on field: ID
        Unique Index: PLAYER_IDX on fields: NUMBER, NAME, TEAM

EXPLAIN (OPT_ESTIMATES on) SELECT * FROM hockey.hockey WHERE number BETWEEN 20 AND 25 AND position = 'Goalie';

Project ID, NUMBER, NAME, POSITION, TEAM
 Filter (HOCKEY.POSITION = 'Goalie')
 Filter (HOCKEY.NUMBER >= 20)
 Filter (HOCKEY.NUMBER <= 25)
   Record fetch HOCKEY (cost: 378.4, est. rows: 4)
     Bitmap filter
       Bitmap scan PLAYER_IDX (NUMBER[>=20:<=25] NAME[] TEAM[]) (cost: 100.4, est. keys: 4)