Chapter 15: Query Plan Generation

CQL offers a way to run SQLite’s EXPLAIN QUERY PLAN command for all the SQL statements used in a CQL file using a set of special compilation steps.

Generating query plans is inherently complicated. Any given stored procedure might include many SQL statements, each of which has a plan. To get the plans, those statements must be executed in the appropriate mode. In order for them to execute, whatever tables, views, and user-defined functions they use must exist. The statements can have any number of parameters, those have to be swapped out because they might come from anywhere. When run in --rt query_plan mode, the compiler accomplishes all of this by analyzing the original code and creating entirely new code. Running this new code creates the schema and, with the appropriate transforms, runs all the SQL statements in the original to give us the query plans. The process requires many steps as we’ll see below.

Query Plan Generation Compilation Steps

TIP: The following steps are used in ./repl/go_query_plan.sh, you can run it to get a quick demonstration of this feature in action . The rest of the section explains how query plan generation works and some of its quirks.

To execute query plans for a given CQL file, the following commands need to be run:

CQL_FILE= # The CQL file to compile
CQL_ROOT_DIR= # Path to cql directory
CQL=$CQL_ROOT_DIR/out/cql

# Generate Query Plan Script
$CQL --in $CQL_FILE --rt query_plan --cg go-qp.sql

# Compile and link CQL artifacts, with a main C file query_plan_test.c
$CQL --in go-qp.sql --cg go-qp.h go-qp.c --dev
cc -I$CQL_ROOT_DIR -I. -c $CQL_ROOT_DIR/query_plan_test.c go-qp.c
cc -I$CQL_ROOT_DIR -I. -O -o go_query_plan go-qp.o query_plan_test.o $CQL_ROOT_DIR/cqlrt.c -lsqlite3

# Run and generate query plans
./go_query_plan

In order to flexibily create query plans, CQL uses --rt query_plan to generate a second CQL script that returns query plans for each SQL statement used in a given file.

The CQL repository provides the file query_plan_test.c that can be used as the “main” function, otherwise you can make your own.

NOTE: >When compiling the CQL file generated by --rt query_plan, the --dev flag is required.

Special Handling of CQL features in Query Plan Generation

CQL’s query planner generator modifies the usage of the following features to allow SQLite run EXPLAIN QUERY PLAN successfully:

• Variables
• User Defined Functions
• Conditionals in Shared Fragments
• Virtual Tables
• Table-Valued Funcxtions

CAUTION: Generating query plans for CQL files that use table valued functions, or virtual tables results in the creation of similarly named “normal” tables which will be using in the query instead of the virtual table or table -valued function. This means that the query plan output won’t be quite right, especially if these items had complex interiors with nested SQLite queries, but it also means the code that generates the query plans does not need to have all of your native bindings for the relevant modules and functions which make it possible to actually run the queries out of context in a standalone build.

Variables

Variables used in SQL statements are stubbed into constants. The exact value varies depending on the type of the variable, but it is always equivalent to some form of "1".

...
SELECT *
FROM my_table
WHERE id = x;
...

...
EXPLAIN QUERY PLAN
SELECT *
FROM my_table
WHERE my_table.id = nullable(1);
...

User Defined Functions

Read Functions on details about Function Types.

Since the implementation of UDFs in a CQL file do not affect SQLite query plans, CQL’s query plan script automatically creates no-op stubs to be used instead.

Conditionals in Shared Fragments

Read CQL Query Fragments on details about shared fragments

Only one branch of a conditional is chosen for query plan analysis. By default this will be the first branch, which is the initial SELECT statement following the IF conditional. The branch to analyze can be configured with the cql:query_plan_branch @attribute

Here’s an example of cql:query_plan_branch being used:

[[shared_fragment]]
[[query_plan_branch=1]]
CREATE PROC frag2(y int)
BEGIN
  IF y == 2 THEN
    SELECT 10 b;
  ELSE IF y == -1 THEN
    SELECT 20 b;
  ELSE
    SELECT 30 b;
  END IF;
END;

EXPLAIN QUERY PLAN
SELECT 20 b;

Setting cql:query_plan_branch=1 selects the second branch. Providing cql:query_plan_branch=2 instead would yield the ELSE clause SELECT 30 b. cql:query_plan_branch=0 would yield SELECT 10 b, which is the same as the default behaviour.