How to Debug Turso Query Execution Using Bytecode EXPLAIN Output

Use EXPLAIN to output the virtual-machine bytecode before execution, or EXPLAIN QUERY PLAN for the high-level query plan, by preparing statements in QueryMode::Explain which returns instruction rows instead of executing them.

Turso re-implements SQLite's virtual-database-engine (VDBE) architecture to execute SQL queries. The ability to debug query execution using Turso's bytecode EXPLAIN output is essential for optimizing performance, verifying optimizer decisions, and identifying missing indexes. This guide covers the two EXPLAIN modes, walks through the source code pipeline where bytecode is generated, and provides practical examples for inspecting execution plans.

Understanding Turso's Bytecode EXPLAIN Modes

Turso supports two distinct diagnostic statements that inspect queries before they run. Both modes generate a Program struct containing bytecode instructions, but they differ in what they return to the caller.

Full Bytecode with EXPLAIN

When you prepend EXPLAIN to any SQL statement, the translator builds a complete Program (list of VM instructions) and returns each instruction as a row. Columns include the instruction address, opcode name, operands (P1-P4), and comments. This output corresponds exactly to the bytecode that the VDBE would execute in normal mode.

According to the tursodatabase/turso source code in core/vdbe/execute.rs, when the connection's QueryMode is set to Explain, the execution loop skips evaluation and instead returns the program's instruction rows as the result set.

High-Level Plans with EXPLAIN QUERY PLAN

EXPLAIN QUERY PLAN returns a four-column table (id, parent, notused, detail) describing the query planner's decisions. The detail column contains human-readable text such as SCAN TABLE users or SEARCH INDEX idx_name. This formatting logic resides in testing/sqltests/src/snapshot/mod.rs, which handles the pretty-printing of plan nodes.

How Turso Generates EXPLAIN Output

The path from SQL text to diagnostic output follows five stages across the codebase:

1. Tokenization – The lexer in parser/src/lexer.rs recognizes the EXPLAIN keyword and emits a TK_EXPLAIN token.
2. Parsing – In parser/src/parser.rs (around line 258), the parser branches on TK_EXPLAIN to set an explain flag on the statement node.
3. Mode Selection – The QueryMode enum defined in types.rs distinguishes between Normal and Explain. When preparing a statement, the explain flag is converted into QueryMode::Explain.
4. Translation – The translator in core/translate/ builds a Program exactly as it would for normal execution, populating opcodes like OpenRead, IdxSearch, or Scan.
5. Execution – In core/vdbe/execute.rs, the VM checks the QueryMode. If it is Explain, the function returns the instruction list rather than entering the execution loop.

Because the bytecode generation path is identical for both modes, the EXPLAIN output accurately represents what the engine will execute.

Debugging Query Execution with Bytecode

Inspecting bytecode helps you verify that the optimizer made the expected decisions. Look for these patterns in the output:

• Access Paths – Confirm that IdxSearch opcodes appear for indexed columns. A Scan opcode on a large table indicates a missing index or stale statistics.
• Join Algorithms – Identify whether the planner chose a hash join (HashJoin opcode), merge join, or nested loop.
• Ordering – Check when cursors are opened (OpenRead) relative to filter evaluation to spot redundant row fetches.

Practical Examples

CLI Usage

The tursodb CLI provides immediate access to both EXPLAIN modes:

# Output full bytecode instructions

tursodb -q "EXPLAIN SELECT name FROM users WHERE id = 42"

# Output the high-level query plan

tursodb -q "EXPLAIN QUERY PLAN SELECT name FROM users WHERE id = 42"

The first command returns columns: addr, opcode, p1, p2, p3, p4, and comment. The second returns the four-column plan table.

Programmatic Inspection in Rust

You can capture EXPLAIN output programmatically by preparing statements with the EXPLAIN prefix. The connection automatically sets QueryMode::Explain when it detects this prefix.

use turso::Connection;

fn print_bytecode(conn: &Connection, sql: &str) -> turso::Result<()> {
    // Prepare automatically switches to QueryMode::Explain
    let stmt = conn.prepare(&format!("EXPLAIN {}", sql))?;
    
    for row in stmt.iter()? {
        let (addr, opcode, p1, p2, p3, p4, comment): (
            i64, String, i64, i64, i64, Option<String>, Option<String>
        ) = row?;
        
        println!(
            "{:3} {:20} {:4} {:4} {:4} {:8?} {}",
            addr, opcode, p1, p2, p3, p4, comment.unwrap_or_default()
        );
    }
    Ok(())
}

This prints the same instruction rows you would see in the CLI, allowing you to assert on specific opcodes in unit tests.

Identifying Missing Indexes

To verify index usage, compare the query plan with the bytecode:

let plan = conn.exec_rows("EXPLAIN QUERY PLAN SELECT * FROM logs WHERE timestamp > 1000")?;
let bytecode = conn.exec_rows("EXPLAIN SELECT * FROM logs WHERE timestamp > 1000")?;

// Check plan for SCAN vs SEARCH
// Check bytecode for Scan opcode vs IdxSearch

If the plan shows SCAN TABLE logs and the bytecode contains a Scan opcode without a preceding OpenRead on an index cursor, the query is performing a full table scan. Create an index on timestamp and re-run to see IdxSearch appear in the bytecode.

Summary

• Turso generates bytecode in core/translate/ and executes it via the VDBE in core/vdbe/execute.rs.
• EXPLAIN returns the raw VM instructions (address, opcode, operands) by setting QueryMode::Explain and returning the Program rows instead of executing.
• EXPLAIN QUERY PLAN returns a condensed four-column description of the planner's strategy, formatted by snapshot testing utilities in testing/sqltests/src/snapshot/mod.rs.
• The parser recognizes EXPLAIN in parser/src/parser.rs (line ~258) after tokenization in parser/src/lexer.rs.
• Use bytecode inspection to verify index usage, join strategies, and cursor opening order.

Frequently Asked Questions

What is the difference between EXPLAIN and EXPLAIN QUERY PLAN in Turso?

EXPLAIN outputs the low-level virtual-machine bytecode that Turso would execute, showing every instruction address, opcode, and operand. EXPLAIN QUERY PLAN outputs a high-level tree showing scan operations, index searches, and join orders without the underlying VM instructions. Use EXPLAIN when you need to see exactly how the engine will traverse cursors, and use EXPLAIN QUERY PLAN for a quick overview of the optimizer's strategy.

Where does Turso store the bytecode generation logic?

The bytecode generation logic resides in the core/translate/ directory, which converts the AST into a Program struct. The execution logic that distinguishes between normal execution and EXPLAIN mode is located in core/vdbe/execute.rs, where the code checks the QueryMode enum defined in types.rs to decide whether to run instructions or return them as rows.

How can I identify missing indexes from EXPLAIN output?

Look for Scan opcodes in the bytecode output or SCAN TABLE messages in the query plan. If you see a Scan on a column that should be indexed, check that the corresponding IdxSearch or Seek opcode is missing. Creating an index on the filtered column and re-running EXPLAIN should replace the Scan with IdxSearch and change the plan from SCAN to SEARCH.

Can I capture EXPLAIN output programmatically in Rust?

Yes. When you call Connection::prepare() with a SQL string prefixed by EXPLAIN, the connection automatically sets the statement's QueryMode to Explain. Iterating over the prepared statement yields rows containing the bytecode instructions. You can also use exec_rows() to fetch the entire result set into a vector for analysis.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}