If I create a table in SQLite that has only the PRIMARY KEY index, is there any role for running ANALYZE after a bulk insert, or will that key automatically have been kept up-to-date by necessity? In this case, I am inserting all data at once, and there will never be any records that are deleted.
What if the scenario is slightly different: the table is WITHOUT ROWID, and the PRIMARY KEY is a clustered index on several columns -- does that change the wisdom here?
The documentation says:
Statistics gathered by ANALYZE are not automatically updated as the content of the database changes. If the content of the database changes significantly, or if the database schema changes, then one should consider rerunning the ANALYZE command in order to update the statistics.
Here, "significantly" means that the new data changes the characteristics so much that the index is no longer as useful, or that it is much more useful than previously.
However, for a primary key, the selectivity usually does not change, so it should not be necessary to run ANALYZE after larger inserts.
(With or WITHOUT ROWID does not make any difference for this.)
You should run analyze if significant change occurs. For example, as the number of rows in the table changes, although the selectivity does not change, the optimal join order in a multi join query may be completely different. Also, in a new table, if you do not run analyze, sqlite will do not know anything about its statistics:
sqlite> create table a(col1 integer primary key);
sqlite> insert into a values(4);
sqlite> select * from sqlite_stat1;
Error: no such table: sqlite_stat1
sqlite> analyze;
sqlite> select * from sqlite_stat1;
a||1
The only case I can think where analyze is not needed, is if you create such a table with a clustered index (using the INTEGER PRIMARY KEY in the create table as before) and you only want to use it for simple single-table queries like select id where id>N. In such case sqlite has really no other option but to use the clustered index.
