That's a query optimizer complexity trade-off. SQLite is targeted for embedded usage, and a smarter query optimizer would make SQLite much larger, cutting into

That's a query optimizer complexity trade-off. SQLite is targeted for embedded usage, and a smarter query optimizer would make SQLite much larger, cutting into one of its main strengths. You're better off using SQLite as a scaled-down RDBMS and moving to postgres when necessary. Lua's design makes similar trade-offs, and they comment on this in http://www.lua.org/doc/sblp2005.pdf http://www.lua.org/doc/sblp2005.pdf: "For instance, just the control-flow analysis of Bigloo, an optimizer Scheme compiler, is more than ten times larger than the whole Lua implementation." It's the same reason the core language doesn't have regular expressions. (Well, that, and LPEG.)