SQLite 3.8.0 was just released; this is the major new feature and a lot more interesting to read.
I have only had one noticeable interaction with the previous query planner, but unfortunately it was a bad one. We had just upgraded from the version of sqlite that had no planner to the first version that did attempt to optimize queries and it foiled our attempts at carefully hand tuned table ordering. It worked fine, except on the databases it didn’t, and then it took forever. I can’t recall if the cross join trick to disable optimization was present at the time, but we solved it by manually looping and joining in c++ code! (The top most table really did return so few results it was reasonable to do this.)
Anyway, I share this because we were pretty badly burned because somebody went and upgraded without really thinking about it, and this is the rare kind of sqlite change that can backfire in unexpected ways. (I was also kind of angry because I wrote the affected queries and component and was therefore blamed for the slowdown, but never consulted regarding the sqlite upgrade.)