# Explain

> Inspect the query plan of any read operation with .explain().

URL: https://better-drizzle.com/docs/querying/explain

Every read operation in Better Drizzle returns an **explainable result**: a lazy thenable, awaited like a promise, with an extra `.explain()` method. Calling `.explain()` sends an `EXPLAIN` for the compiled SQL and returns the plan.

## Basic usage

Chain `.explain()` off any read call to get the query plan:



**better-drizzle**

```ts  
const {
	driver, // "sqlite" | "pg" | "mysql"
	operation, // "findMany"
	statements, // array of ExplainStatement
} = await client.users.findMany({ where: { active: true } }).explain();
```

**Drizzle**

```ts  
import { eq, sql } from 'drizzle-orm';

// no explain API: wrap the query builder in a raw EXPLAIN (PostgreSQL)
const query = db.select().from(users).where(eq(users.active, true));
const plan = await db.execute(sql`EXPLAIN ${query}`);
```

## What runs, and when

* Read methods are lazy, like Drizzle's own query builders: calling one builds the read but runs nothing. The read, and its query hooks, run only when the result is awaited (or consumed through `then`, `catch`, `finally`, or `Promise.all`), and at most once. `.explain()` alone never runs the read.
* `.explain()` runs only `EXPLAIN` statements for the SQL the read would send. Query hooks do not run for it. Cursor probes that depend on page contents appear as conditional statements; `.explain()` does not fetch the page to decide whether they will run.
* A plain `EXPLAIN` (the default) only plans the statement. With `analyze: true`, PostgreSQL (`EXPLAIN (ANALYZE true)`) and MySQL (`EXPLAIN ANALYZE`) **execute** the statement to collect real timings. SQLite ignores `analyze`.

```ts
const result = client.users.findMany({ where: { active: true } });

// nothing has run yet; this only plans the statements
const { statements } = await result.explain();

// the read runs here
const users = await result;
```

> **analyze executes the query:** Reads have no side effects, but `analyze: true` still costs a full query
> execution. Avoid it on hot paths and against large tables in production.

## Result shape

`ExplainResult` contains four standard fields and an optional `deferredProbes` field:

| Field               | Type                          | Description                                                                                                           |
| ------------------- | ----------------------------- | --------------------------------------------------------------------------------------------------------------------- |
| `driver`            | `'sqlite' \| 'pg' \| 'mysql'` | The dialect that produced the plan                                                                                    |
| `operation`         | `string`                      | The operation name (e.g. `"findMany"`, `"paginate"`)                                                                  |
| `statements`        | `ExplainStatement[]`          | One or more statement details                                                                                         |
| `deferredRelations` | `object[]`                    | Relation queries from `include` / relation `select` that depend on root rows, so they are described but not explained |
| `deferredProbes`    | `{ key, reason }[]?`          | Cursor probes whose SQL needs a returned page-edge row, so they cannot be explained without running the read          |

Each `deferredRelations` entry has `path`, `table`, `cardinality` (`'one' \| 'many'`), `filtered`, `sorted`, `paginated`, and `through` (the junction table, for many-to-many).

Each `ExplainStatement` contains:

| Field            | Type                          | Description                                                                                                 |
| ---------------- | ----------------------------- | ----------------------------------------------------------------------------------------------------------- |
| `key`            | `string`                      | Role of the statement: `"data"`, `"total"`, `"count"`, `"exists"`, `"probe:hasNext"`, `"probe:hasPrevious"` |
| `condition`      | `string?`                     | When present, describes when a cursor probe runs                                                            |
| `sql`            | `string`                      | The compiled SQL **without** the `EXPLAIN` prefix                                                           |
| `params`         | `unknown[]`                   | Bound parameter values                                                                                      |
| `appliedOptions` | `Partial<ExplainOptions>`     | Options the driver actually recognized                                                                      |
| `ignoredOptions` | `Array<keyof ExplainOptions>` | Options the driver silently dropped                                                                         |
| `raw`            | `unknown`                     | The raw database output (shape varies by driver)                                                            |

## Multi-statement operations

Most operations produce a single statement. Two exceptions:

**`paginate`** produces two: the data query and the count query.

```ts
const result = await client.users
	.paginate({ limit: 10, orderBy: { id: 'asc' } })
	.explain();

console.log(result.statements.map((s) => s.key));
// ["data", "total"]
```

**`cursor`** includes an indexed existence check in the data query for simple primary-key pages. An empty page may also need a `hasNext` / `hasPrevious` probe. The plan includes that probe with `condition: 'when the data page is empty'`, even if the page would be populated:

```ts
const result = await client.users
	.cursor({
		after: previousCursor,
		limit: 25,
		orderBy: { id: 'asc' },
	})
	.explain();

console.log(result.statements.map((s) => s.key));
// ["data", "probe:hasPrevious"]

console.log(result.statements[1].condition);
// "when the data page is empty"
```

For other cursor query shapes, the probe binds a cursor from the first or last returned row. `.explain()` reports it under `deferredProbes` instead of showing SQL with guessed parameters.

## Options

Pass an `ExplainOptions` object to control the `EXPLAIN` output:

```ts
const plan = await client.users
	.findMany({ where: { active: true } })
	.explain({
		analyze: true,
		verbose: true,
		costs: false,
		timing: true,
		summary: false,
		name: 'users.findMany',
		comment: 'active user lookup',
		timeoutMs: 5000,
	});
```

### Options reference

| Option      | Type      | PostgreSQL | MySQL   | SQLite  | Description                                                                               |
| ----------- | --------- | ---------- | ------- | ------- | ----------------------------------------------------------------------------------------- |
| `analyze`   | `boolean` | Yes        | Yes     | ignored | Execute the statement and report actual execution statistics                              |
| `verbose`   | `boolean` | Yes        | ignored | ignored | Include output schema of each plan node                                                   |
| `costs`     | `boolean` | Yes        | ignored | ignored | When `false`, omit estimated startup and total cost                                       |
| `timing`    | `boolean` | Yes        | ignored | ignored | When `false`, omit actual time even with `analyze`                                        |
| `summary`   | `boolean` | Yes        | ignored | ignored | When `false`, omit the summary line                                                       |
| `name`      | `string`  | label only | ignored | ignored | A label for the call. Reported in `appliedOptions` on PostgreSQL but not added to the SQL |
| `comment`   | `string`  | Yes        | ignored | ignored | Block comment prepended to the explained query (`*/` is escaped)                          |
| `timeoutMs` | `number`  | Yes        | Yes     | Yes     | Timeout for the EXPLAIN itself. Throws `RAW_TIMEOUT`; `0` or less throws immediately      |

> **Note:** Unsupported options are silently ignored. Check `statement.ignoredOptions`
> to detect when a flag had no effect on the current dialect.

## Checking applied vs ignored options

```ts
const {
	statements: [stmt],
} = await client.users
	.findMany({ where: { active: true } })
	.explain({ analyze: true, verbose: true });

// On SQLite, both are ignored
console.log(stmt.ignoredOptions); // ["analyze", "verbose"]

// On PostgreSQL, both are applied
console.log(stmt.appliedOptions); // { analyze: true, verbose: true }
```

## Plugin transforms are reflected

`.explain()` runs the plugin transform pipeline. If a plugin modifies `where`, `select`, or other args, the explained SQL reflects those changes:

```ts
import { better } from 'better-drizzle';
import { definePlugin } from 'better-drizzle/plugins';

// Injects { active: true } into every findMany
const forceActive = definePlugin({
	id: 'force-active',
	transform(operation) {
		if (operation.kind === 'findMany')
			operation.where = (
				operation.where
					? { AND: [operation.where, { active: true }] }
					: { active: true }
			) as typeof operation.where;
		return operation;
	},
});

const client = better(db, { plugins: [forceActive] });

const {
	statements: [{ sql }],
} = await client.users.findMany({ where: { id: 1 } }).explain();

// The SQL includes the plugin-injected active filter
console.log(sql);
// contains "active" clause
```

> **Warning:** Query hooks (`beforeQuery`, `afterQuery`) are **skipped** during `.explain()`.
> Only plugin transforms apply.

## Cross-dialect behavior

| Feature        | PostgreSQL                    | MySQL                   | SQLite                   |
| -------------- | ----------------------------- | ----------------------- | ------------------------ |
| EXPLAIN prefix | `EXPLAIN (OPTIONS) ...`       | `EXPLAIN [ANALYZE] ...` | `EXPLAIN QUERY PLAN ...` |
| `analyze`      | Executes the statement        | Executes the statement  | ignored                  |
| `verbose`      | Extra columns                 | ignored                 | ignored                  |
| `costs`        | Toggle cost display           | ignored                 | ignored                  |
| `timing`       | Toggle timing                 | ignored                 | ignored                  |
| `summary`      | Toggle summary                | ignored                 | ignored                  |
| `name`         | Reported as applied, not sent | ignored                 | ignored                  |
| `comment`      | Prepended as block comment    | ignored                 | ignored                  |
| `timeoutMs`    | Yes                           | Yes                     | Yes                      |

## Which operations support `.explain()`?

| Operation                            | Supports `.explain()` |
| ------------------------------------ | --------------------- |
| `findMany`                           | Yes                   |
| `findFirst`                          | Yes                   |
| `findOne`                            | Yes                   |
| `findUnique`                         | Yes                   |
| `count`                              | Yes                   |
| `exists`                             | Yes                   |
| `paginate`                           | Yes                   |
| `cursor`                             | Yes                   |
| `create`, `createMany`               | No                    |
| `update`, `updateMany`, `updateEach` | No                    |
| `delete`, `deleteMany`               | No                    |
| `upsert`, `upsertMany`               | No                    |

- [Reads](/docs/querying/reads)

- [Pagination](/docs/querying/pagination)

- [Hooks](/docs/advanced/hooks)

- [Plugins](/docs/plugins/overview)