SQLite reuses the work stored in expression indexes

An expression index already contains the result of a computation. Reading the original columns and calculating that result again can waste both arithmetic and table access. SQLite 3.19.0, released on 22 May, teaches the engine to try to reuse the expression value available in an index. The change makes the stored index entry useful beyond locating matching rows.

This is a useful way to understand the release as a whole. Several changes remove work whose result SQLite already has, or move a condition to a point where it can discard rows sooner. None requires a new query language. The application continues to express the result it needs, while the engine has more ways to avoid intermediate operations.

I value that kind of improvement in SQLite because its execution cost belongs directly to the embedding application. Removing a table read or a repeated expression reduces work inside the caller. The practical benefit depends on the query, but the mechanism is concrete enough to understand without a headline benchmark.

An index contains computed values

The expression-index regression tests in the released source include an index on a+1. A query then selects both that expression and its quoted form, with ordering on the first result column. The test expects the computed values and their quoted representations to agree.

CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(1, one);
INSERT INTO t1 VALUES(2, two);
INSERT INTO t1 VALUES(3, three);
CREATE INDEX i2 ON t1(a+1);
SELECT a+1, quote(a+1) FROM t1 ORDER BY 1;

Here the index expression is also an output expression. Reusing its stored result avoids reconstructing it from the original column when the chosen execution path already has the indexed value. The release notes describe this as an attempt, so the presence of an expression index does not promise that every reference will use it. Query shape and the selected plan still determine which values are available.

The same test file exercises an index on b || x. Its queries compare that expression with an uppercase string, first under the default comparison and then with COLLATE nocase. The expected answers differ. This is a useful correctness boundary for the optimization. An expression result may be reusable, but the comparison surrounding it must retain the requested collation.

The distinction matters when reading performance changes. An index is a stored representation of values, and the executor can sometimes use those values directly. That opportunity does not permit it to discard the semantics of an operation applied to them. The tests combine reuse with surrounding SQL operations to check the result, rather than treating fewer computations as sufficient evidence of correctness.

Avoiding materialized intermediate results

Another change extends subquery flattening to views on the right side of a LEFT JOIN. Flattening lets SQLite incorporate an eligible view into the surrounding query. That gives the planner a different representation to work with instead of requiring the view to remain a separate query boundary.

The release description is deliberately narrower than a claim that all outer joins now flatten. It names views on the right side and an improved optimization. Outer-join semantics still determine which rows must remain in the result when the joined relation has no match. An execution transformation has to preserve those rows, even if eliminating an intermediate relation would otherwise seem attractive.

SQLite also reuses the same materialization when a view appears more than once in a query. This addresses a different situation. When a view does need materialization, repeated references can share the computed result. Flattening and reuse therefore provide two distinct ways to reduce work associated with views. One changes the query representation, while the other avoids producing the same intermediate result again.

These details make views more useful as a way to organize SQL. A view introduces a named query that an application can reference, but that source-level organization need not dictate a separate execution for every occurrence. Version 3.19.0 expands the cases where the engine can make a less repetitive execution choice.

Moving filters before grouping

The release also transfers eligible HAVING terms into WHERE. Eligibility is specific. The term must use only columns mentioned in GROUP BY. Such a condition can filter rows before grouping instead of waiting until the grouped result exists.

Moving an arbitrary aggregate condition would change its meaning. A condition concerning an aggregate result needs the group to exist before the engine can evaluate it. The stated restriction identifies conditions whose inputs are already available in the grouping columns. That is why the optimization concerns particular terms of a clause, rather than a wholesale rewrite of every HAVING expression.

A separate optimization affects indexed DISTINCT queries. When an appropriate index is available, SQLite tries to skip to the next distinct entry instead of stepping through rows that contribute the same value. This reduces repeated visits within a run of equivalent index entries. As with expression reuse, the improvement depends on having a suitable index and an execution path that can exploit it.

I prefer these specific descriptions to a broad claim that the planner is faster. They explain which schema and query choices expose the opportunity. They also distinguish reduced work per row from avoiding rows altogether, which are different reasons for an execution to become cheaper.

Compatibility extends beyond query answers

The release includes an authorizer change that application authors need to distinguish from planner improvements. For each referenced table from which a query extracts no columns, SQLite now invokes the SQLITE_READ authorizer callback once with an empty column name. Authorization code must handle that documented case. A query can refer to a table even when it does not extract a named column from it.

Several other changes remove unrelated work. Updates that leave foreign-key columns untouched can avoid unnecessary foreign-key processing. Changes to unrelated tables no longer cause unnecessary invalidation of incremental BLOB handles. The JSON extraction function caches and reuses parses of its input text. Each change has its own eligibility conditions, so none establishes a universal speedup for an application.

Correctness changes also accompany the optimizations. The notes fix a REPLACE problem that could leave duplicate rowids in a corrupt database, and extend integrity_check to identify that condition. They correct foreign-key checking for WITHOUT ROWID tables. JSON parsing rejects leading zeros in numeric constants and control characters inside strings, and limits recursion depth to avoid excessive stack use. Applications encountering these inputs may observe changed behavior even when their query plans remain the same.

The expression-index change is especially appealing to me because it uses information the database already maintains. An application that has paid to store an expression result can now benefit when the executor needs that result again. The release tests make the required boundary explicit by checking both the returned value and the comparison semantics around it.