Details
-
Improvement
-
Resolution: Done
-
Critical
-
2.8.2
-
Security Level: Public
Description
TL;DR: We can help developers ensure their queries run fast the first time by exposing API that calls SQLite's "pragma optimize" and "analyze" commands.
Details: A couple of developers have reported queries that run quite slowly the first time, but then quickly thereafter, either immediately or after re-opening the db.
What's happening seems to be that the query optimizer initially chooses an inefficient plan (based on poor query design and/or lack of knowledge about the contents of indexes), but later on SQLite has had a chance to gather some index statistics that help the optimizer choose a better plan.
Usually that "later on" is after the db is closed/reopened, since we call "pragma optimize" just before closing the connection, but in one CBSE they say it happens as soon as the next time they run the query, which surprises me. Anyway.
If we give devs the ability to call "ANALYZE", they can do this after major changes to the database such as the initial sync. It'll take a little while but will ensure queries will run fast.
ANALYZE has an "analysis_limit" parameter that can make it run faster by sampling only a limited number of rows. So it would be good to offer two forms of this, one unlimited/slow and the other limited/fast.
API Proposal: Add two new mode constants to LiteCore's c4db_maintenance(), one for a full analyze, the other for a quick analyze.
We'd need to add corresponding API to CBL itself, of course.