SQLite: Downsides of ANALYZE

Does the ANALYZE command have any downsides (except a开发者_运维问答 slighty larger db)? If not, why is not executed by default?

There is another downside. The ANALYZE results may cause the query planner to ignore indexes that you really want to use.

For example suppose you have a table with a boolean column "isSpecial". Most of the rows have isSpecial = 0 but there are a few with isSpecial = 1.

When you do a query SELECT * FROM MyTable WHERE isSpecial = 1, in the absence of ANALYZE data the query planner will assume the index on isSpecial is good and will use it. In this case it will happen to be right. If you were to do isSpecial = 0 then it would still use the index, which would be inefficient, so don't do that.

After you have run ANALYZE, the query planner will know that isSpecial has only two values, so the selectivity of the index is bad. So it won't use it, even in the isSpecial = 1 case above. For it to know that the isSpecial values are very unevenly distributed it would need data that it only gathers when compiled with the SQLITE_ENABLE_STAT4 option. That option is not enabled by default and it has a big downside of its own: it makes the query plan for a prepared statement depend on its bound values, so sqlite will re-prepare the statement much more often. (Possibly every time it's executed, I don't know the details)

tl;dr: running ANALYZE makes it almost impossible to use indexes on boolean fields, even when you know they would be helpful.

Short answer: it may take more time to calculate than time saved.

Unlike indices the ANALYZE-statistics are not kept up-to-date automatically when data is added or updated. You should rerun ANALYZE any time a significant amount of data has been added of updated.