Thank you for explaining this. I guess you're right about query planner deciding to avoid index usage based on stats.
22 июля 2011, 18:30 от Richard Hipp <d...@sqlite.org>: > On Fri, Jul 22, 2011 at 9:48 AM, Григорий Григоренко > <grigore...@mail.ru>wrote: > > > > > > > Please post the results of: > > > > > > SELECT * FROM sqlite_stat1; > > > > > > > > tbl = log > > idx = idxlog_kind_computer_process_who_id_msg_created_at > > stat = 2815667 563134 563134 469278 74097 1 1 1 > > > > The first number on "stat" is the number of rows in the table. The 2nd > number is the average number of rows that have the same value for the first > column of the index. The 3rd number is the average number of rows that have > the same value for the first 2 columns of the index. And so forth. > > There are a huge number of rows that have the same value for the first 4 > terms of this index, which shows us that this is a really lousy index. > Ideally, you want the second number in the "stat" column to be something > small, like 10. > > If you do not run ANALYZE, SQLite has no way of knowing that the index is > mostly useless. SQLite assumes that the index is a good one, and that the > 2nd integer in "stat" is 10. And it therefore tries to use the index. But > since the index is so bad, the resulting performance is slow. > > After running ANALYZE, SQLite realizes that the index is lousy and avoids > using it. Hence, performance is much better. > > > > > > tbl = log > > idx = idxlog_kind_computer_process_id_who_msg_created_at > > stat = 2815667 563134 563134 469278 1 1 1 1 > > > > tbl = log > > idx = idxlog_kind_computer_id_process_who_msg_created_at > > stat = 2815667 563134 563134 1 1 1 1 1 > > > > tbl = log > > idx = idxlog_kind_id_computer_process_who_msg_created_at > > stat = 2815667 563134 1 1 1 1 1 1 > > > > tbl = log > > idx = idxlog_kind_computer_process_who_msg_created_at > > stat = 2815667 563134 563134 469278 74097 2 2 > > > > tbl = log > > idx = idxlog_created_at > > stat = 2815667 106 > > > > > > -- > D. Richard Hipp > d...@sqlite.org > > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
