Identifying Index Usage (EXPLAIN)

This section assumes you have run the steps at Examples of Debugging a Slow Query.

For purpose of this discussion, let’s say you have a query and this query is running less than optimally:

SELECT firstname, lastname, playerId FROM Players WHERE firstname = 'Mike';

What’s going on? The first step will be to determine whether or not the query is using the index you expect it to use. This can be done with the EXPLAIN command (see EXPLAIN). Given any SQL query (sqlquery), NuoDB SQL will accept the syntax “EXPLAIN sqlquery”. This will display the query plan produced by NuoDB’s query optimizer. The query plan shows what decisions the NuoDB optimizer made to retrieve the data for the query result set. With the plan in hand you can make changes to the query or the database structure (add/remove indexes) to improve query performance.

The output from the EXPLAIN command is the same as the value specified in the SYSTEM.QUERYSTATS EXPLAIN column.

NuoDB EXPLAIN can also be used on the DML statements UPDATE, REPLACE, and DELETE. The EXPLAIN function will display the query plan that NuoDB’s optimizer produced for selecting the rows to be manipulated by the DML statement.

Using nuosql, EXPLAIN can be invoked on a SELECT statement against the PLAYERS table, as follows:

Select Players with last name "Brooke".

SELECT firstname, lastname, playerID FROM players WHERE lastname='Brooke';
 FIRSTNAME  LASTNAME  PLAYERID
 ---------- --------- ---------
    Bob      Brooke   brookbo01

Explain the previous query (see EXPLAIN for a full description of the NuoDB explain output).

EXPLAIN SELECT firstname, lastname, playerID FROM players WHERE lastname='Brooke';
Optimized using v3 optimizer
Project PLAYERS.FIRSTNAME, PLAYERS.LASTNAME, PLAYERS.PLAYERID
  Filter (PLAYERS.LASTNAME = 'Brooke') (est. rows: 787)
    Table scan PLAYERS (cost: 22560.0, est. rows: 7520)
----------------

Now show the Explain of a query with a WHERE clause using an indexed column, FIRSTNAME. This time the Explain output shows a "Bitmap scan" instead of a "Table scan". This means the optimizer chose to scan the index IDX_PLAYERS_FIRSTNAME to retrieve the data for the query result set, rather than doing a full or exhaustive table scan.

EXPLAIN SELECT firstname, lastname, playerID FROM players WHERE firstname='Kurt';
Optimized using v3 optimizer
Project PLAYERS.FIRSTNAME, PLAYERS.LASTNAME, PLAYERS.PLAYERID
  Record fetch PLAYERS (cost: 10.4, est. rows: 3)
    Bitmap filter
      Bitmap scan IDX_PLAYERS_FIRSTNAME (FIRSTNAME[='Kurt']) (cost: 2.6, est. keys: 3)

The output of EXPLAIN also contains optimizer estimates:

Statistic Meaning

cost

This represents an estimated number of operations required to execute the query. It is somewhat arbitrary and only has meaning to the user when compared with cost of other queries. Lower numbers are better.

est. keys

This is the estimated number of rows used in the index scan for the query. Again, lower numbers are better.

est. rows

This is the estimated number of rows the operator outputs. Again, lower numbers are better.

rows

This is estimated number of rows used by the index for this query.

As per the explain output of the above query

      Bitmap scan IDX_PLAYERS_FIRSTNAME (FIRSTNAME[='Kurt']) (cost: 2.6, est. keys: 3)

the optimizer estimates 3 keys to qualify for the predicate FIRSTNAME equal to “Kurt”.

In contrast, for the query

EXPLAIN SELECT firstname, lastname, playerID FROM players WHERE firstname='Mike';
Optimized using v3 optimizer
Project PLAYERS.FIRSTNAME, PLAYERS.LASTNAME, PLAYERS.PLAYERID
  Record fetch PLAYERS (cost: 16372.0, est. rows: 4093)
    Bitmap filter
      Bitmap scan IDX_PLAYERS_FIRSTNAME (FIRSTNAME[='Mike']) (cost: 4093.0, est. keys: 4093)

the optimizer estimates 4093 rows to qualify. As more rows have to be processed, the cost estimate for this query is also higher than for the more selective query for players with firstname “Kurt”.

For more information on the Query Plan see EXPLAIN.