SQLite 3.41.0 preserves index choices through query rewriting

Adding a constraint to a query can make its index disappear. A February SQLite forum report demonstrated that behavior with an index on abs(value). One predicate matched the indexed expression, but an additional equality allowed constant propagation to replace the column inside that expression. The transformed query no longer contained the expression that the index matcher needed.

SQLite 3.41.0, released on February 21, improves that interaction. It also extends expression-index use in aggregate queries and makes the planner more aware of covering indexes. These changes concern the planner's ability to recognize work that an existing index can avoid. Their usefulness depends on the query and schema, so they do not imply a uniform performance improvement.

I value SQLite's ability to execute SQL inside an application without a separate database service. That makes the planner part of the application's own CPU and latency costs. A release that better recognizes an existing index can matter without requiring a schema redesign.

How one optimization obstructed another

Richard Hipp reduced the forum report to a table with an integer column and an expression index. The essential statements were small enough to explain the problem directly.

CREATE TABLE t1(x INT);
INSERT INTO t1 VALUES(1);
CREATE INDEX idx ON t1(abs(x));

SELECT * FROM t1 WHERE abs(x)=1 AND x=1;

The equality x=1 lets the optimizer infer a constant value for x elsewhere in the condition. Replacing the argument of abs(x) with that constant produces a condition equivalent to abs(1)=1 AND x=1. The query still describes the same requested row in this example. However, the expression index is on abs(x), and the rewritten condition has lost that expression.

Hipp explained that this transformation left no matching term for the index. He also described a workaround using unary plus in the equality to prevent constant propagation. Later in the same February thread, he linked a change that restored the same query plan with and without the optimization. The reporter confirmed that the change worked.

This is a useful example of optimizer composition. Constant propagation has enough information to simplify the expression, while index selection benefits from retaining its original relationship to the stored index expression. Each operation has a local reason for what it does. The planner also needs to retain enough information for a later operation to find a useful access method.

The release notes describe the improvement as an increased ability to use indexed expressions even after constant propagation modifies the expression. That wording matters. It identifies a recognition problem, without claiming that every algebraically equivalent expression now matches every index. The concrete forum example supports a narrower conclusion about this interaction.

Reusing expression results

The change continues work described in the SQLite 3.40.0 release notes. That release could extract expression values from expression indexes where practical instead of recomputing them. An expression index therefore offers a potential benefit beyond finding candidate rows. Its stored value can sometimes supply a result that execution would otherwise calculate again.

Version 3.41.0 adds use of indexed expressions within aggregate queries containing GROUP BY. This makes aggregate processing another place where the planner can recognize the value already present in an index. The release also improves awareness of covering indexes and adjusts predicted runtimes accordingly. That is a planning-cost change, so an index can become more attractive because the estimate better reflects the work it avoids.

The connection between these changes is the representation of available data. A useful value may already exist in an index even though the SQL text does not look like a simple column lookup. Recognizing that value can affect both the execution generated for a chosen plan and the planner's estimate of which plan to choose.

My interest here is in avoiding unnecessary work inside the embedded engine. The benefit is specific to queries that can use the stored expression or covering index. A claim about general application speed would require evidence beyond the release notes.

Preparation now participates in cancellation

The same release changes how long-running preparation responds to application control. Calls to sqlite3_prepare() and related interfaces now invoke the progress handler and react to sqlite3_interrupt(). Preparing a complicated statement can itself be substantial work, so waiting until row execution to observe cancellation leaves part of the request outside that control.

The 3.41.0 API header explains the historical distinction. The progress handler originally ran from the bytecode engine. Preparation could already invoke it indirectly when reparsing the schema required bytecode execution. Beginning with this release, analysis and code generation for complex queries can invoke it directly.

The callback's rules remain consequential. A nonzero return interrupts the operation. The connection has one progress handler at a time, and registering a replacement cancels the previous handler. A callback must not perform operations that modify the invoking connection. The header explicitly includes preparation and stepping within that restriction.

Those details prevent an application from treating the callback as an unrestricted opportunity to run more SQL. It is a control point within an operation already using the connection. A cancellation flag checked by application code fits that purpose. Recursive preparation through the same connection conflicts with the documented contract.

The callback interval is also approximate and expressed in virtual-machine instructions. It should not be described as a wall-clock deadline or an exact count of parser operations. Extending callbacks into preparation improves responsiveness for complex statements while preserving an API whose scheduling is cooperative.

Seeing the plan and its estimates

SQLite 3.41.0 also adds sqlite3_stmt_scanstatus_v2(). Availability depends on compiling with SQLITE_ENABLE_STMT_SCANSTATUS, a condition stated in both the release notes and API documentation. Applications distributing SQLite therefore need to know how their library was built before depending on this diagnostic interface.

The new interface accepts flags, including SQLITE_SCANSTAT_COMPLEX. With that flag, status information can cover all query-plan elements reported by EXPLAIN QUERY PLAN. Without it, information concerns the scan and search loops. The older scan-status interface corresponds to the new one with zero flags.

The CLI uses this work for richer plan output and adds .scanstats est to include planner estimates in profiles. These facilities help distinguish a changed cost estimate from a changed statement result. For the expression-index case, the immediate question is whether the rewritten condition still permits the intended index access. The release supplies both a planner improvement and more detailed ways to inspect the work a plan performs.