3.3. Reading the Execution Plan and Optimizing Your SQL Statements

Documentation

Home » Documentation » Performance Guide

3.3. Reading the Execution Plan and Optimizing Your SQL Statements

Within the execution plan is an ordered representation of the winning plan. The plan can be read bottom up to understand the order in which the plan is executed. So, for example, looking at the SELECT statement in the Hello World example, we see that the Select-sql.txt plan file contains the following:

1 SQL: SELECT HELLO, WORLD FROM HELLOWORLD  WHERE DIALECT = ?;
2 COST: 6.0
3 PLAN:
4
5 RETURN RESULTS TO STORED PROCEDURE
6  INDEX SCAN of "HELLOWORLD" using "SYS_IDX_SYS_PK_10018_10019" (unique-scan covering)

The first two lines list the SQL statement itself and a "cost" for the selected plan. The cost value is an abstract internal calculation used to determine which of the many possible plans the compiler chooses as the winner. In general, the more complex the SQL statement, the higher the cost for any given plan. However, this cost value does not have any bearing on the actual potential performance of the statement itself. It is simply a relative ordering of the possible plans.

The real content of the execution plan appears in the "Plan" section, starting with line 3.

As mentioned before it is often easiest to read the plans from the bottom up. So in this instance, how the SQL statement is executed is by:

  • Performing an indexed scan of the HELLOWORLD table (line 6)

  • Returning the selected row(s) to the stored procedure (line 5)

You will notice that line 6 is indented to indicate it is a child of the preceding statement. In other words, it must be completed before the results can be returned.

You will also notice that the scan of the HELLOWORLD table specifies the index as SYS_IDX_SYS_PK_10018_10019. The use of system-generated values is not particularly meaningful, but is a consequence of the index not being explicitly named in the database schema. If you want to use the execution plans to evaluate your schema and stored procedures, it is a good idea to explicitly name the indexes in your DDL. For example, if we modify the Hello World example to explicitly name the partitioning column:

CREATE TABLE HELLOWORLD (
   HELLO VARCHAR(15),
   WORLD VARCHAR(15),
   DIALECT VARCHAR(15) NOT NULL,
   CONSTRAINT PK_DIALECT PRIMARY KEY (DIALECT)
);

The execution plan incorporates the stated name, becoming more readable:

SQL: SELECT HELLO, WORLD FROM HELLOWORLD  WHERE DIALECT = ?;
COST: 6.0
PLAN:

RETURN RESULTS TO STORED PROCEDURE
 INDEX SCAN of "HELLOWORLD" using "SYS_IDX_PK_DIALECT_10018" (unique-scan covering)

Of course, planning for a SQL statement accessing one table with only one condition is not very difficult. The execution plan becomes far more interesting when evaluating more complex statements. For example, if you look at the execution plans for the Voter example that comes with the VoltDB software, you can see a more complex execution plan for the GetStateHeatmap stored procedure:

SQL: SELECT contestant_number, state, SUM(num_votes) AS num_votes 
     FROM v_votes_by_contestant_number_state GROUP BY contestant_number, 
     state ORDER BY 2 ASC, 3 DESC;
COST: 8000000.0
PLAN:

RETURN RESULTS TO STORED PROCEDURE
 ORDER BY (SORT)
  AGGREGATION ops: sum
   RECEIVE FROM ALL PARTITIONS
    SEND PARTITION RESULTS TO COORDINATOR
     AGGREGATION ops: sum
      SEQUENTIAL SCAN of "V_VOTES_BY_CONTESTANT_NUMBER_STATE"

In this example you see an execution plan for a multi-partition stored procedure. Again, reading from the bottom up, the order of execution is:

  • At each partition, perform a sequential scan of the votes-per-contestant-and-state table.

  • Use an aggregate function to sum the votes for each contestant.

  • Return the results from each partition to the initiator that is coordinating the multi-partition transaction.

  • Sum the results from all partitions.

  • Combine and sort the results.

  • And finally, return the results to the stored procedure.

3.3.1. Evaluating the Use of Indexes

What makes the execution plans important is that they can help you optimize your database application by pointing out where the data access can be improved, either by modifying indexes or by changing the join order of queries. Let's start by looking at indexes.

VoltDB uses information about the partitioning column to determine what partition needs to execute a single-partitioned stored procedure. However, it does not automatically create an index for accessing records in that column. So, for example, in the Hello World example, if we remove the primary key (DIALECT) on the HELLOWORLD table, the execution plan for the select statement also changes:

SQL: SELECT HELLO, WORLD FROM HELLOWORLD  WHERE DIALECT = ?;
COST: 2000000.0
PLAN:

RETURN RESULTS TO STORED PROCEDURE
 SEQUENTIAL SCAN of "HELLOWORLD"

Note that the first operation has changed to a sequential scan of the HELLOWORLD table, rather than a indexed scan. Since the Hello World example only has a few records, it does not take very long to look through five or six records looking for the right one. But imagine doing a sequential scan of an employee table containing tens of thousands of records. It quickly becomes apparent how important having an index can be when looking for individual records in large tables.

There is an incremental cost associated with inserts or updates to tables containing an index. But the improvement on read performance often far exceeds any cost associated with writes. For example, consider the flight application that is used as an example throughout Chapter 3 of the Using VoltDB manual. The FLIGHT table is a replicated table with an index on the FLIGHT_ID, which helps for transactions that join the FLIGHT and RESERVATION tables looking for a specific flight.

However, one of the most common transactions associated with the FLIGHT table is customers looking for flights during a specific time period; not by flight ID. In fact, looking up flights by time period is estimated to occur at least twice as often as looking for a specific flight.

The execution plan for the LookupFlight stored procedure using the original schema looks like this:

SQL: SELECT * FROM FLIGHT WHERE origin=? AND destination=? 
     AND departtime>? AND departtime < ?;
COST: 2000000.0
PLAN:

RETURN RESULTS TO STORED PROCEDURE
 SEQUENTIAL SCAN of "FLIGHT"

Clearly, looking through a table of 2,000 flights without an index 10,000 times a second will impact performance. So it makes sense to add another index to improve this transaction. Because the condition is a range (greater than or less than) rather than checking for an exact value match, it needs a tree rather than a hash index.

VoltDB creates tree indexes by default. However, you can explicitly specify the type of index by adding the string "tree" or "hash" (upper or lower case) to the constraint name. For example, we can explicitly specify that we want a tree index by using "tree" in the index name:

CREATE TABLE Flight (
   FlightID INTEGER UNIQUE NOT NULL,
   DepartTime TIMESTAMP NOT NULL,
   Origin VARCHAR(3) NOT NULL,
   Destination VARCHAR(3) NOT NULL,
   NumberOfSeats INTEGER NOT NULL,
   PRIMARY KEY(FlightID)
);
CREATE INDEX flightTimeTreeIdx ON FLIGHT ( departtime);

After adding the tree index, the execution plan changes to use the index:

SQL: SELECT * FROM FLIGHT WHERE origin=? AND destination=? 
     AND DEPARTTIME>? AND DEPARTTIME < ?;
COST: 144.0
PLAN:

RETURN RESULTS TO STORED PROCEDURE
 INDEX SCAN of "FLIGHT" using "FLIGHTTIMETREEIDX" (range-scan covering)

Indexes are not required for every database query. For very small tables or infrequent queries, an index could be unnecessary overhead. However, in most cases and especially frequent queries over large datasets, not having an applicable index can severely impact performance.

When tuning your VoltDB database application, one useful step is to review the execution plans to make sure your application is not performing any unexpected sequential (non-indexed) scans. You can use the Linux grep command to do this, like so:

$ grep -i "sequential scan" debugoutput/statement-winner-plans/*

3.3.2. Evaluating the Table Order for Joins

The other information that the execution plans provides, in addition to the use of indexes, is the order in which the tables are joined.

Join order often impacts performance. Normally, when joining two or more tables, you want the database engine to scan the table that produces the smallest number of matching records first. That way, there are fewer comparisons to evaluate when considering the other conditions. However, at compile time, VoltDB does not have any information about the potential sizing of the individual tables and must make its best guess based solely on the table schema, query, and any indexes that are defined.

For example, assume we have a database that correlates employees to departments. There is a DEPARTMENT table and an EMPLOYEE table, with a DEPT_ID column that acts as a foreign key. But departments have managers, who are themselves employees. So there is a MANAGER table that also contains both a DEPT_ID and an EMP_ID column. The relationship of the tables looks like this:

Most transactions look up employees by their employee ID or their department ID. So indexes are created for those columns. However, say we want to look up all the employees that report to a specific manager. Now we need to join the MANAGER table (to get the department ID), the DEPARTMENT table (to get the department name), and the EMPLOYEE table (to get the employees' names). VoltDB does not know, in advance when compiling the catalog, that there will be many more employees than departments or managers. As a result, the winning plan might look like the following:

SQL: SELECT dept.dept_name, dept.dept_id,emp.emp_id, emp.first_name, 
     emp.last_name, manager.emp_id FROM MANAGER, DEPARTMENT AS Dept, 
     EMPLOYEE AS Emp WHERE manager.emp_id=? AND manager.dept_id=dept.dept_id 
     AND manager.dept_id=emp.dept_id ORDER BY emp.last_name, emp.first_name
COST: 8000000.0
PLAN:

RETURN RESULTS TO STORED PROCEDURE
 ORDER BY (SORT)
  NESTLOOP INDEX JOIN
  inline (INDEX SCAN of "DEPARTMENT" using "DEPTIDX" (unique-scan covering))
   NESTLOOP INDEX JOIN
   inline (INDEX SCAN of "MANAGER" using "MGRIDX" (unique-scan covering))
    RECEIVE FROM ALL PARTITIONS
     SEND PARTITION RESULTS TO COORDINATOR
      SEQUENTIAL SCAN of "EMPLOYEE"

Clearly, performing a sequential scan of the employees (since the department ID has not been identified yet) is not going to provide the best performance. What you really want to do is to join the MANAGER and DEPARTMENT tables first, to identify the department ID before joining the EMPLOYEE table so the last join can take advantage of the appropriate index.

For cases where you are joining multiple tables and know what the optimal join order would be, VoltDB lets you specify the join order as part of the SQL statement definition. Normally, you declare a new SQLstmt class by specifying the SQL query only. However, you can provide a second argument specifying the join order as a comma-separated list of table names and aliases. For example, the declaration of the preceding SQL query, including join order, would look like this:

public final SQLStmt FindEmpByMgr = new SQLStmt(
    "SELECT dept.dept_name, dept.dept_id, emp.emp_id, " +
    "emp.first_name, emp.last_name, manager.emp_id " +
    "FROM MANAGER, DEPARTMENT AS Dept, EMPLOYEE AS Emp " + 
    "WHERE manager.emp_id=? AND manager.dept_id=dept.dept_id " +
    "AND manager.dept_id=emp.dept_id " +
    "ORDER BY emp.last_name, emp.first_name",
    "manager,dept,emp");

Note that where the query defines an alias for a table — as the preceding example does for the DEPARTMENT and EMPLOYEE tables — the join order must use the alias name rather than the original table name. Also, if a query joins six or more tables, you must specify the join order or VoltDB reports an error when it compiles the project.

Having specified the join order, the chosen execution plan changes to reflect the new sequence of operations:

SQL: SELECT dept.dept_name, dept.dept_id,emp.emp_id, emp.first_name, 
     emp.last_name, manager.emp_id FROM MANAGER, DEPARTMENT AS Dept, 
     EMPLOYEE AS Emp WHERE manager.emp_id=? AND manager.dept_id=dept.dept_id 
     AND manager.dept_id=emp.dept_id ORDER BY emp.last_name, emp.first_name
COST: 8000000.0
PLAN:

RETURN RESULTS TO STORED PROCEDURE
 ORDER BY (SORT)
  RECEIVE FROM ALL PARTITIONS
   SEND PARTITION RESULTS TO COORDINATOR
    NESTLOOP INDEX JOIN
    inline (INDEX SCAN of "EMPLOYEE" using "EMPDEPTIDX" (unique-scan covering))
     NESTLOOP INDEX JOIN
     inline (INDEX SCAN of "DEPARTMENT" using "DEPTIDX" (unique-scan covering))
      SEQUENTIAL SCAN of "MANAGER"

The new execution plan has at least three advantages over the default plan:

  • It starts with a sequential scan of the MANAGER table, a table with 10-20 times fewer rows than the EMPLOYEE table.

  • Because MANAGER and DEPARTMENT are replicated tables, all of the initial table scanning and filtering can occur locally within each partition, rather than returning the full EMPLOYEE data from each partition to the initiator to do the later joins and sorting.

  • Because the join order retrieves the department ID first, the execution plan can utilize the index on that column to improve the scanning of EMPLOYEE, the largest table.