Query plan

The Query tab on every database has an Explain button next to Run. Click it to see SQLite’s EXPLAIN QUERY PLAN output without executing the statement — useful when you want to know whether a query is using an index, scanning the whole table, or pulling rows from a subquery.

The plan is rendered as an indented tree using the same parent → id relationships SQLite emits, so nested loops and subqueries show up as visually-nested nodes.

REST

POST /v1/db/:ns/:db/explain
{ "sql": "SELECT * FROM orders WHERE customer_id = ?", "params": [42] }

Returns:

[
  { "id": 2, "parent": 0, "detail": "SEARCH orders USING INDEX idx_orders_customer (customer_id=?)" }
]

A console (cookie) version exists at /api/namespaces/:ns/databases/:db/explain with the same body.

SDK

const plan = await db.explain(
  "SELECT * FROM orders WHERE customer_id = ?",
  [42]
);
for (const row of plan) {
  console.log(row.id, row.parent, row.detail);
}

Permissions

• Read-only — Explain doesn’t execute the SQL, but it does parse it.
• Token roles still apply — a readonly token can run EXPLAIN INSERT but a readwrite token can’t EXPLAIN CREATE TABLE (the underlying SQL has to satisfy the role check).
• Table scope still applies — EXPLAIN SELECT FROM customers is rejected if customers isn’t in the token’s scope.

Reading the output

A few common patterns:

• SCAN <table> — full-table scan. Often a sign you need an index, but cheap on small tables.
• SEARCH <table> USING INDEX <name> — using an index. What you want for hot queries.
• SCAN <table> USING COVERING INDEX <name> — index has every column the query needs; SQLite never visits the table itself.
• USE TEMP B-TREE FOR ORDER BY — query is materializing a temp tree to sort. If this shows up on a hot path, an index on the ORDER BY column would help.

If you’ve enabled the Insights tab, slow queries that fall in the recorded bucket are good candidates to Explain — drop the SQL into the editor and click Explain.