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

# Generate UDF stubs
$CQL --in $CQL_FILE --rt udf --cg go-qp-udf.h go-qp-udf.c

# 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 go-qp-udf.c
cc -I$CQL_ROOT_DIR -I. -O -o go_query_plan go-qp.o go-qp-udf.o query_plan_test.o $CQL_ROOT_DIR/cqlrt.c -lsqlite3

# Run and generate query plans
./go_query_plan

Contrary to what one might expect, rather than providing query plans directly, CQL uses --rt query_plan to generate a second CQL script that returns query plans for each SQL statement used.

A separate command, --rt udf is required to generate any stubbed user defined functions that are used in the original script. Afterwards, the generated query plan script, udf stubs needs to compiled like any CQL file and run by a "main" function that needs to be created separately.

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

caution

Generating query plans of CQL files that use table valued functions, or virtual tables is not supported.

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".

original.sql

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

query_plan.sql

...
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 expects stubs generated by cql --rt udf 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:

original.sql

@attribute(cql:shared_fragment)
@attribute(cql: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;

query_plan.sql

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.