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:

Specify one or more parameters in any order.
Separate multiple parameters with a comma.
Enclose the parameter(s) in parentheses.
Optionally parameterize the ON or OFF setting in the same way that you would specify parameters in a SQL statement.

ANALYZE { 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

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:

The EXPLAIN command for the SQL SELECT statement.
The Select List, which shows the fully qualified names of each of the columns being retrieved.
A Bitmap index 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 a 16% selectivity ratio based on the number of rows being returned by the index scan.
A Boolean sieve Between operator, which represents the sql statement predicate, as defined by the WHERE clause "hockey.number BETWEEN 20 AND 25".

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 ***