---
title: "Inspect a SQLite query plan in C#"
url: https://recursiveai.org/onelite/cookbook/inspect-sqlite-query-plan/
description: "Inspect index choices and actual table-loop row counts with OneLite’s structured query plans, using a runnable C# example before and after adding an index."
version: "1.0.0"
---

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

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/inspect-sqlite-query-plan/inspect-sqlite-query-plan.zip)

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.

```shell
dotnet add package RecursiveAI.OneLite
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
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](https://recursiveai.org/onelite/cookbook/inspect-sqlite-query-plan/inspect-sqlite-query-plan.zip), 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.

```csharp
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.

```csharp
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
```

## Related recipes

- [Recover rows from a damaged SQLite file in C#](https://recursiveai.org/onelite/cookbook/recover-rows-from-damaged-sqlite-file/)
- [Execute a SQLite command batch in C#](https://recursiveai.org/onelite/cookbook/execute-sqlite-command-batch/)

## 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](https://recursiveai.org/onelite/docs/plans/) 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](https://www.sqlite.org/eqp.html); its output format is not the format shown here.
