How to Explain SQLite Query Plans in Python

Build a Python function that runs EXPLAIN QUERY PLAN on SQLite in-memory tables and prints the optimizer's execution plan for any SELECT statement.

Medium Python 3.9+ Aug 9, 2026 Database scaling & optimization 14 views 0 copies

sqlite query-plan optimization index explain

Python code

43 lines

Python 3.9+

import sqlite3

def explain_query(sql: str) -> str:
    """Return the SQLite query plan for the given SQL statement."""
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()
    
    # Create sample data for a realistic plan
    cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
    cursor.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, amount REAL)")
    cursor.executemany(
        "INSERT INTO users VALUES (?, ?)",
        [(1, "Alice"), (2, "Bob"), (3, "Carol")]
    )
    cursor.executemany(
        "INSERT INTO orders VALUES (?, ?, ?)",
        [(1, 1, 50.0), (2, 1, 75.0), (3, 2, 100.0)]
    )

    # Fetch the query plan
    cursor.execute(f"EXPLAIN QUERY PLAN {sql}")
    plan_rows = cursor.fetchall()
    
    # Format the plan output
    header = f"Query: {sql}\n"
    header += "-" * 40 + "\n"
    plan_text = header
    
    if plan_rows:
        for row in plan_rows:
            plan_text += f"  {row[0]}|{row[1]}|{row[2]}|{row[3]}\n"
    else:
        plan_text += "  (empty plan)\n"
    
    conn.close()
    return plan_text

if __name__ == "__main__":
    # Test with a join query
    result = explain_query(
        "SELECT name, amount FROM users JOIN orders ON users.id = orders.user_id"
    )
    print(result)

Output

stdout

Query: SELECT name, amount FROM users JOIN orders ON users.id = orders.user_id
----------------------------------------
  2|0|0|SCAN users
  6|0|0|SEARCH orders USING AUTOMATIC COVERING INDEX (user_id=?)

How it works

The EXPLAIN QUERY PLAN prefix tells SQLite to return a textual description of how it will execute the query instead of running it. Each row in the result maps to a step in the execution tree: the first column is the node ID, the second the parent ID, the third the auxiliary flag (not used for simple plans), and the fourth is the human-readable operation. By creating small in-memory tables with known data, you get a reproducible plan without touching the filesystem. Inspecting the plan reveals whether the optimizer uses a full scan (SCAN) or an index search (SEARCH), which is invaluable for spotting missing indexes and slow joins.

Common mistakes

Using EXPLAIN instead of EXPLAIN QUERY PLAN, which returns bytecode rather than readable steps
Forgetting the WHERE or JOIN condition, resulting in an unexpected SCAN instead of SEARCH
Not closing the connection or using a named file when sticking to :memory: causes data loss per run

Variations

Call `cursor.execute('EXPLAIN QUERY PLAN ' + sql)` directly without wrapping it in a function.

Real-world use cases

Debugging slow production queries by replaying them against a sanitized dataset to see if the optimizer uses indexes.
Benchmarking alternative index designs before applying DDL changes to a live database.
Teaching teammates how SQLite decides between full scans and index lookups during query review.

How to Explain SQLite Query Plans in Python

Python code

Output

How it works

Common mistakes

Variations

Real-world use cases

More from Database scaling & optimization

Tutorials

Quizzes

Python code

Output

How it works

Common mistakes

Variations

Real-world use cases

More from Database scaling & optimization

Keep learning

Tutorials

Quizzes