Uploaded image for project: 'Couchbase Lite'
  1. Couchbase Lite
  2. CBL-1666

Allow apps to trigger SQLite index optimization directly

    XMLWordPrintable

Details

    • Improvement
    • Status: Closed
    • Critical
    • Resolution: Done
    • 2.8.2
    • 3.0
    • LiteCore
    • 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.

      Attachments

        No reviews matched the request. Check your Options in the drop-down menu of this sections header.

        Activity

          jens Jens Alfke added a comment -

          I have an implementation of this in Litecore, in branch feature/maintenance-analyze-CBL1666. (It's currently on the dev branch, but should be easy to rebase onto master.)

          jens Jens Alfke added a comment - I have an implementation of this in Litecore, in branch feature/maintenance-analyze-CBL1666 . (It's currently on the dev branch, but should be easy to rebase onto master.)

          People

            pasin Pasin Suriyentrakorn
            jens Jens Alfke
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Gerrit Reviews

                There are no open Gerrit changes

                PagerDuty