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.

ANALYZE_GRAPH {ON | OFF}

This option outputs a Graphviz representation of the explain plan.

Copy and paste the diagraph PlanGraph {} to any Graphviz viewer to generate a graphical file to visualize the explain plan. This is useful for debugging performance characteristics of complex query plans.

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.

    Example 1: Use the OPT_ESTIMATES parameter
    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)
    Example 2: Use the ANALYZE_GRAPH parameter to visualize the execution plan
    EXPLAIN (ANALYZE_GRAPH ON) SELECT /* top goal scorers in single season */
  firstname, lastname, birthyear, s.year "YEAR PLAYED", s.goals
FROM scoring s
JOIN players using (playerid)
ORDER BY s.goals desc
LIMIT 10;

Optimized using v3 optimizer
Analyzed plan graph:
digraph PlanGraph {
    compound = true
    labeljust = l
    rankdir=BT
    fontname=Courier
    label = "select /* top goal scorers in single season */\l  firstname, lastname, birthyear, s.year "YEAR PLAYED", s.goals\lfrom scoring s\ljoin players using (playerid)\lorder by s.goals desc\llimit 10\l"
    subgraph cluster1 { fontname=Helvetica
        style=rounded; label="Result"
        result1 [ shape = record, fontsize = 10, label= "{result format:|1: FIRSTNAME(FIRSTNAME)(varchar(32))|2: LASTNAME(LASTNAME)(varchar(32))|3: BIRTHYEAR(BIRTHYEAR)(integer)|4: YEAR PLAYED(YEAR)(integer)|5: GOALS(GOALS)(integer)}"]
        vdesc1 [ shape = record, fontsize = 10, fontsize=14 label=< <B>Cumulative time</B>: 64.174ms (<B>This node time</B>: 25us) <BR/>Open: 64.134ms<BR/>Next: 18us > ]
    }
    subgraph cluster2 { fontname=Helvetica
        style=rounded; bgcolor="#d6fff2"; label = "Sort"
        vdesc2 [ shape = record, fontsize = 10, fontsize=14 label=< <B>Cumulative time</B>: 64.149ms (<B>This node time</B>: 8.978ms) <BR/>Open: 64.111ms<BR/>Next: 17us > ]
        sortKeys2 [ shape = record, fontsize = 10, label= "{sort keys:|1: S.GOALS desc}"]
    }
    subgraph cluster3 { fontname=Helvetica
        style=rounded; bgcolor="#FFFFFF";
        label = "Hash join on (S.PLAYERID = PLAYERS.PLAYERID)"
        vdesc3 [ shape = record, fontsize = 10, fontsize=14 label=< <B>Cumulative time</B>: 55.171ms (<B>This node time</B>: 26.388ms) <BR/>Open: 12us<BR/>Next: 54.840ms > ]
    }
    subgraph cluster4 { fontname=Helvetica
        style=rounded; bgcolor="#e6fafc";
        label = "Table scan S (SCORING)(\"HOCKEY\".\"SCORING\")"
        vdesc4 [ shape = record, fontsize = 10, fontsize=14 label=< <B>Cumulative time</B>: 21.292ms (<B>This node time</B>: 21.292ms) <BR/>Open: 0us<BR/>Next: 21.291ms > ]
    }
    edge [ arrowhead = normal style = solid ]
    subgraph cluster5 { fontname=Helvetica
        style=rounded; bgcolor="#e6fafc";
        label = "Table scan PLAYERS(\"HOCKEY\".\"PLAYERS\")"
        vdesc5 [ shape = record, fontsize = 10, fontsize=14 label=< <B>Cumulative time</B>: 7.491ms (<B>This node time</B>: 7.491ms) <BR/>Open: 0us<BR/>Next: 7.490ms > ]
    }
    edge [ arrowhead = normal style = solid ]
    edge [ arrowhead = normal style = solid ]
    vdesc4 -> vdesc3 [ lhead=cluster3 fontsize=14 fontweight=bold label=< <B>46.0K</B> <I>(Estimate: 46.0K)</I>> penwidth=5];
    vdesc5 -> vdesc3 [ lhead=cluster3 fontsize=14 fontweight=bold label=< <B>7.5K</B> <I>(Estimate: 7.5K)</I>> penwidth=1];
    edge [ arrowhead = normal style = solid ]
    vdesc3 -> vdesc2 [ lhead=cluster2 fontsize=14 fontweight=bold label=< <B>46.0K</B> <I>(Estimate: 46.0K)</I>> penwidth=1];
    edge [ arrowhead = normal style = solid ]
    vdesc2 -> vdesc1 [ lhead=cluster1 fontsize=14 fontweight=bold label=< <B>10.0</B> <I>(Estimate: 10.0)</I>> penwidth=1];
}

Project PLAYERS.FIRSTNAME, PLAYERS.LASTNAME, PLAYERS.BIRTHYEAR, SCORING.YEAR PLAYED, SCORING.GOALS (actual rows: 10, time: 64.174ms, batches: 2)
  Sort S.GOALS DESC limit 10 (cost: 236940.5, est. rows: 10, actual rows: 10, time: 64.149ms, total memory: 5KB, peak memory: 5KB, batches: 2)
    Hash join on (S.PLAYERID = PLAYERS.PLAYERID) (cost: 190974.5, est. rows: 45966, actual rows: 45966, time: 55.171ms, batches: 1438)
      Table scan S (SCORING) (cost: 137909.5, est. rows: 45966, actual rows: 45966 in 23 record, 68 data atoms (imported 0 (0B)), time: 21.292ms, batches: 1438)
      Hash (keys: 7520, rows: 7520, memory: 884KB, build time: 18.802ms, probe hits: 45966/45966, extracted rows: 45966)
        Table scan PLAYERS (cost: 22562.0, est. rows: 7520, actual rows: 7520 in 4 record, 15 data atoms (imported 0 (0B)), time: 7.491ms, batches: 236)

    Copy and paste diagraph PlanGraph {} into Dreampuf or any other Graphviz viewer to generate the graphical file.

Visual representation of the explain plan
Figure 1. Visual representation of the explain plan