Open topic with navigation

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 quickly 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. This query execution plan is later executed 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 update SQL statements or database structure to improve performance. For example, you might decide to add/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.

Note: 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

explain_option

To obtain additional information about the execution plan, specify one or more of the parameters described below. The default is that each of these parameters is set to OFF. To provide explain_option:

ClosedOPT_ESTIMATES { ON | OFF }

Set OPT_ESTIMATES to ON to cause the EXPLAIN command output to include cost estimates for each index or table search that the optimizer chooses for 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.

ClosedOPT_FULL_COST { ON | OFF }

OPT_FULL_COST is an extension of the functionality provided by the OPT_ESTIMATES option. In addition to the cost and selectivity estimates shown for the index accesses, use the OPT_FULL_COST option to show the total cost associated with accessing a given table. This total cost includes the cost for the index accesses and the cost for fetching table records that were filtered by the indexes.

ClosedANALYZE { ON | OFF }

Set ANALYZE to ON to cause the EXPLAIN command to evaluate the SQL  statement, but instead of calculating cost estimates based on index statistics that NuoDB generates, this ANALYZE option forces the SQL statement to be executed. This allows the query engine to collect actual statistics for each operator of the execution plan. And because the SQL statement is executed, the execution time per plan operator is also collected. These statistics are displayed in EXPLAIN format annotated with these counter and time information per plan operator.

This ANALYZE option supports only SQL SELECT statements. See Understanding EXPLAIN Command Output for a detailed description of the additional information provided by this option.

EXPLAIN (ANALYZE ON) UPDATE hockey SET team = 'Boston' WHERE id = 1;
Explain analyze on statement other than select or union is not supported.  Found: Update
ClosedPARTITION_IDS { ON | OFF }

Set PARTITION_IDS to ON to cause the EXPLAIN command output to list the table partition IDs for each table partition that the optimizer searched to retrieve rows (see Use Cases for Table Partitions and Storage Groups). This ID value is displayed for only table(s) that are partitioned. This option is ignored for table(s) that are not partitioned. See Understanding EXPLAIN Command Output for a detailed description of the additional information provided by this option.

Note: A very useful combination of explain_options for SQL SELECT statements is the EXPLAIN(ANALYZE ON,OPT_ESTIMATES ON) command. These options allow you to compare the actual execution plan costs, calculated by the ANALYZE option, with the estimated execution plan costs, calculated by the OPT_ESTIMATES option.

Sample Output

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

This EXPLAIN command output shows the following:

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;
 Select
  List
     HOCKEY.HOCKEY.ID (1)
     HOCKEY.HOCKEY.NUMBER (1)
     HOCKEY.HOCKEY.NAME (1)
     HOCKEY.HOCKEY.POSITION (1)
     HOCKEY.HOCKEY.TEAM (1)
  Boolean sieve
    Between
       HOCKEY.HOCKEY.NUMBER (1)
      "20" (integer) 
      "25" (integer) 
    Index Scan HOCKEY.HOCKEY  (1)
      Bitmap Index Scan PLAYER_IDX [cost: 32.6, interval sel: 16.000%, predicate sel: 16.000%, keys: 5, rows: 5]
        "20" (integer) 
        *** null ***
        *** null ***
        "25" (integer) 
        *** null ***
        *** null ***