[anarazel]

> But that's exactly the point. If the SQLite got the plan right at first, you know it will always "get it right" in the future, i.e. the query will finish. Maybe it'll go from being optimal to somewhat less optimal, but it'll still be fine -- or at worst you'll see a very, very gradual slowdown (as your tables grow) that you'll have time to investigate later.

Reality is much more complex. Unless your data set size and the distribution continously stay the same, the algorithm that made complete sense initially (say an index nested loop) will often not make any sense anymore after some growth. Imagine a nested loop over a couple hundred thousand rows where the inner side's index lookup always has to hit storage. That just won't perform and will, if run concurrently, slow down the entire system to a standstill because it consumes all the IOPS. In many cases that slowdown won't be gradual at all - it'll be fast as long the working set fits entirely into the available memory, and will stop very soon afterwards (depends on value distribution).