---
id: ch15
title: "Chapter 15: Query Plan Generation"
sidebar_label: "Chapter 15: Query Plan Generation"
---
CQL offers a way to run SQLite's [`EXPLAIN QUERY PLAN` command](https://www.sqlite.org/eqp.html) 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](/docs/playground#query-plan-playground). 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:

```bash
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](/cql-guide/ch08) 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`](https://github.com/facebookincubator/CG-SQL/blob/main/sources/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](https://sqlite.org/vtab.html#:~:text=2.-,Table%2Dvalued%20functions,columns%20of%20the%20virtual%20table.) 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"`.

```sql title="original.sql"
...
SELECT *
FROM my_table
WHERE id = x;
...
```

```sql title="query_plan.sql"
...
EXPLAIN QUERY PLAN
SELECT *
FROM my_table
WHERE my_table.id = nullable(1);
...
```

#### User Defined Functions
_Read [Functions](/cql-guide/ch08) 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](/cql-guide/ch14) 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](/cql-guide/x3).

Here's an example of `cql:query_plan_branch` being used:

```sql title="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;
```

```sql title="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.
