Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite

clustered-primary-keyindexprimary-keysqlite

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?

Best Answer

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.)

Related Question