On 9/12/17, Jens Alfke <j...@mooseyard.com> wrote:
> I realize SQLite doesn't have the kind of industrial-strength query
> optimizers that other SQL databases have, but (from an outside perspective)
> this seems like a fairly straightforward optimization. SQLite is already
> doing some similar tricks to recognize matching sub-expressions when it
> applies an expression-based index to a query, for example.
The query planner in SQLite will hold its own against most others.
But we deliberately omit common subexpression elimination (CSE). This
is because our research shows that out of the millions of queries that
SQLite compiles every second, only a very tiny fraction would actually
benefit from CSE, but checking for CSE is expensive in both memory and
CPU cycles, and all queries would have to pay the extra checking
overhead whether they benefit or not.
A traditional compiler like GCC is free to use as much memory, time,
and code space to implement esoteric optimizations as it wants,
because compilation happens separately from the application and the
build product will be reused many times and so the cost of compilation
is amortized over many executions. But a query planner in an RDBMS
(which is really just a compiler that translates the SQL programming
language into some low-level representation - byte code in the case of
SQLite) is more constrained because the compilation happens at
application run-time and the number of uses of the build product is
approximately 1. And so when writing a query planner, one must be
careful in the use of memory, time, and code space devoted to
optimizations. This is particularly so for SQLite which is an
Our belief is that CSE would be not worth the extra memory, CPU, and
code space required to implement it since CSE simply does not come up
that often in SQL statements. If, in the future, we find that people
begin coding more complex SQL statements which will more often benefit
from CSE, then we might revisit this decision.
D. Richard Hipp
sqlite-users mailing list