Recursive AI

Inspect a SQLite query plan in C#

OneLite is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

A query plan shows how the database reads your tables: a scan visits a table, while a search uses a key or index to narrow the rows.

Terminal
dotnet add package RecursiveAI.OneLite

Requires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.

C#
using RecursiveAI.OneLite;
 
using var db = Database.Open(":memory:");
using var connection = db.Connect();
using var create = connection.Prepare(
    "CREATE TABLE items(id INTEGER PRIMARY KEY)");
create.Step();
Console.WriteLine(connection.Explain(
    "SELECT * FROM items WHERE id=1")!.Render());
Text
SELECT
`-- SEARCH items USING INTEGER PRIMARY KEY (id=1)  (est. 1 row)
        why: a WHERE term fixes id, so one row is read directly

Unzip the example, then dotnet run.

Full program

The complete example creates items(id INTEGER PRIMARY KEY, category TEXT) with rows (1, 'book'), (2, 'tool') and (3, 'book'), then compares the same query before and after adding an index.

C#
using RecursiveAI.OneLite;
 
using var db = Database.Open(":memory:");
using var connection = db.Connect();
Run("CREATE TABLE items(id INTEGER PRIMARY KEY, category TEXT)");
Run("INSERT INTO items VALUES(1,'book'),(2,'tool'),(3,'book')");
const string sql = "SELECT id FROM items WHERE category='book'";
Console.WriteLine("Before index:");
Console.WriteLine(connection.Explain(sql, analyze: true)!.Render());
Run("CREATE INDEX items_category ON items(category)");
Console.WriteLine("After index:");
Console.WriteLine(connection.Explain(sql, analyze: true)!.Render());
 
void Run(string sql)
{
    using var statement = connection.Prepare(sql);
    while (statement.Step()) { }
}
Text
Before index:
SELECT
`-- SCAN items  (est. 1048576 rows, actual 3 rows in 1 loop)
        why: no WHERE term can be answered by the rowid or an index, so the table is read in full
After index:
SELECT
`-- SEARCH items USING INDEX items_category (category='book')  (est. 10 rows, actual 2 rows in 1 loop)
        why: items_category answers equality on (category); the rows are read from items_category alone: it holds every column the query uses

Row estimates are heuristic: OneLite does not read sqlite_stat1, so est. 1048576 rows is an assumed table size, not a bug or the size of this fixture.

Invalid SQL throws rather than producing a plan; statements without table access return null.

Variations

Collect counters on a prepared statement. Add these lines at the end of the full program.

C#
using var counted = connection.Prepare(sql, collectActualRows: true);
while (counted.Step()) { }
Console.WriteLine(counted.Plan!.Render());
Text
SELECT
`-- SEARCH items USING INDEX items_category (category='book')  (est. 10 rows, actual 2 rows in 1 loop)
        why: items_category answers equality on (category); the rows are read from items_category alone: it holds every column the query uses

The short answer

Use Connection.Explain(sql) to inspect the access path selected by OneLite, and call Render() for readable text. For the C# equivalent of investigating a SQLite EXPLAIN QUERY PLAN, this recipe uses OneLite’s own structured plan API. Its output describes OneLite’s execution, not a plan obtained from a separate SQLite engine. Plain SQL EXPLAIN QUERY PLAN SELECT … does not run in the published OneLite 1.0.0 package: preparing it throws OneLiteException with unsupported: EXPLAIN and returns no plan rows; use Connection.Explain instead.

Pass analyze: true when you also want to run the statement and collect table-loop counters. The full example demonstrates a change from a table scan to an index search; it does not measure elapsed time or establish a speed improvement.

What does explain plan do in SQL?

A plan describes the steps chosen to execute a statement. OneLite returns a QueryPlan tree whose nodes expose Kind, Table, Index, Detail, Reason, Rejected and Children. The text rendering includes the access path and its explanation. A query without table access, such as a schema change, has no plan: Explain returns null.

For joins, nesting expresses loop order. A child table-access node runs inside its parent’s loop. Other nodes can describe sorting, aggregation, materialization or subqueries. Inspect that structure when a query does more than read one table; a single index name does not describe the whole execution.

The plan documentation explains the structured properties. Plan wording is not part of OneLite’s compatibility promise, so keep diagnostic text out of application decisions. Prefer structured fields when displaying plan details in your own tools, while allowing for changes across releases.

How to analyze SQL query execution plan?

Start with the exact statement you want to investigate. Inspect which WHERE terms the access path answers, whether it reads the table or an index, and whether sorting is needed. In this example, category='book' initially has no usable index. After creating items_category, the plan reports a search answering that equality.

The reported actual rows are rows yielded by a table loop before the remaining WHERE filters, not necessarily the final result count. Loops counts loop starts; an inner table’s totals include all outer rows. Counters accumulate when you execute the same counted statement object again. To bind parameters yourself, prepare with collectActualRows: true, bind values, step through the statement and inspect Statement.Plan.

Explain(sql, analyze: true) runs without bound values. Database-changing statements run inside a savepoint that is rolled back. Do not treat it as a substitute for testing application behavior with its real parameters and transaction context.

How do SQLite indexes affect a query plan?

The output shows three visited rows before indexing and two afterward because those are the rows in this fixture. The index also holds every column this query needs, according to the returned reason. That observation applies to this query and schema, not every projection or filter.

OneLite’s estimates are heuristic guesses: it does not read sqlite_stat1. An estimate is therefore not a measured table size or an observed result count. Compare estimated and actual rows with that distinction in mind. For the general meaning of SQLite SCAN and SEARCH terminology, see SQLite’s query-plan guide; its output format is not the format shown here.

All onelite recipes