Match the exact expression used by a SQLite expression index

By Batu · English technical notes

Also published in our primary archive.

SQLite matches an expression index by the expression’s written form, apart from minor syntactic differences; it does not rewrite y+x into x+y. Therefore a query using x+y = 7 can use an index created on x+y, whereas y+x = 7 does not match that index.

This example creates idx_sum on points(x+y), inserts two rows whose sums are both seven, and retrieves EXPLAIN QUERY PLAN output for the two predicates. Planner-detail strings can vary across SQLite releases, so the program prints a normalized transcript while preserving the actual details for its assertions. The first plan contains USING INDEX idx_sum; the reversed predicate does not. The matching result rows are also asserted equal, separating result correctness from access-path selection.

This does not prove a particular elapsed-time difference: the fixture has only two rows, and query-planner choices can depend on schema, data, and SQLite version. It demonstrates this specific expression-index matching rule. SQLite’s official Indexes On Expressions documentation gives the same x+y versus y+x contrast and notes expression indexes arrived in SQLite 3.9.0. The code uses Python’s standard-library sqlite3 module, with no filesystem or network access. No newer Python-specific API is used (Python 3.5+); require SQLite 3.9.0+.

AI-assistance disclosure: this synthetic example was drafted with AI assistance and is intended to be run and adapted by the reader.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE points(x INTEGER, y INTEGER)")
con.execute("CREATE INDEX idx_sum ON points(x+y)")
seed = [(2, 5), (1, 6)]
con.executemany("INSERT INTO points VALUES (?, ?)", seed)

print("schema:", con.execute(
    "SELECT sql FROM sqlite_master WHERE name = 'points'"
).fetchone()[0])
print("index:", con.execute(
    "SELECT sql FROM sqlite_master WHERE name = 'idx_sum'"
).fetchone()[0])
print("seed:", seed)

def plan_for(predicate):
    return con.execute(
        "EXPLAIN QUERY PLAN SELECT x, y FROM points WHERE " + predicate,
        (7,),
    ).fetchone()[3]

exact_plan = plan_for("x+y = ?")
reversed_plan = plan_for("y+x = ?")
exact_uses_index = "USING INDEX idx_sum" in exact_plan
reversed_uses_index = "USING INDEX idx_sum" in reversed_plan
print("x+y = ? plan:", "index idx_sum" if exact_uses_index else "not idx_sum")
print("y+x = ? plan:", "index idx_sum" if reversed_uses_index else "not idx_sum")

exact_rows = list(con.execute("SELECT x, y FROM points WHERE x+y = ?", (7,)))
reversed_rows = list(con.execute("SELECT x, y FROM points WHERE y+x = ?", (7,)))
assert exact_uses_index
assert not reversed_uses_index
assert exact_rows == reversed_rows == [(2, 5), (1, 6)]
con.close()

schema: CREATE TABLE points(x INTEGER, y INTEGER)
index: CREATE INDEX idx_sum ON points(x+y)
seed: [(2, 5), (1, 6)]
x+y = ? plan: index idx_sum
y+x = ? plan: not idx_sum