3.2. Understanding VoltDB Execution Plans

Documentation

VoltDB Home » Documentation » Guide to Performance and Customization

3.2. Understanding VoltDB Execution Plans

VoltDB stores the execution plans for stored procedures along with the schema in the database. There are three methods for reviewing these execution plans. You can:

  • Call the @Explain or @ExplainProc system procedures

  • Use the explain or explainproc directives in sqlcmd

  • Review the execution plans in the VoltDB Management Center Schema tab

The system procedures and sqlcmd directives produce identical output. For example, if you enter the explainproc directive in sqlcmd with the name of a stored procedure, it displays the execution plan for every SQL statement defined in the stored procedure. You get the same results calling the @ExplainProc system procedure. You can see the same information by connecting to the VoltDB Management Center in a web browser. The explain directive and @Explain system procedure allow you to review the execution plan for an ad hoc SQL query by entering the text of the query.

Let's look at the voter sample program as an example. The voter sample has five stored procedures. The Initialize procedure declares three SQL statements. You can see the execution plans for all three statements by starting the sample application, connecting to the server using sqlcmd and using the explainproc directive. You can also get the execution plan for an ad hoc count of the votes table using the explain directive, like so:

$ sqlcmd
1> explainproc Initialize;
 . . .
2> explain select count(*) from votes;
 . . .

In the VoltDB Management Center, which is available from a web browser via http:://{server-name}:8080 by default, you can see the execution plans by navigating to the Schema tab, clicking on Procedures & SQL, and expanding the stored procedure to see the individual statements. The execution plan is displayed in the expanded view. The following example shows the execution plan for InsertContestantStmt in the Initialize stored procedure.