On Fri, Mar 28, 2014 at 7:38 PM, Péli Gergely <p...@t-online.hu> wrote:
> On Fri, 28 Mar 2014 19:29:52 -0400 > Richard Hipp <d...@sqlite.org> wrote: > > What happens if you omit the INDEXED BY clause and instead run ANALYZE? > > Nice, it starts to use the right index. But ANALYZE shouldn't be necessary > in such a simple case, right? > This case just seems simple to you because you an intelligent, rational being. The query planner in SQLite, on the other hand, is a mindless automaton. The problem arises because in the absence real ANALYZE results, the query planner has to guess at the "shape" of each index. There are some simple heuristics used to make that guess. Those heuristics normally work reasonably well, though they failed in this particular instance by guessing that "inappropriate" was more selective than "appropriate", when in fact the opposite is true. On the other hand, the failure was not by much. The results were close and the "inappropriate" index just barely managed to edge out "appropriate" as the chosen index. I put in some real data and ran ANALYZE, and with some good ANALYZE results rather than the heuristically-generated guesses, the "appropriate" index was a clear winner - "inappropriate" wasn't even close. Thanks for providing an interesting test case. I'll see if I can't tweak the ANALYZE-guessing heuristics to do a better job for the next release. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
