Skip to content

Python library methods for calling ANALYZE #366

Closed

Description

@simonw

Relevant documentation: https://www.sqlite.org/lang_analyze.html

Originally posted by @simonw in #365 (comment)

Activity

  1. simonw commented on Jan 7, 2022

    @simonw
    OwnerAuthor

    Annoyingly I use the word "analyze" to mean something else in the CLI - for these features:

    there's only one method with a similar name in the Python library though and that's this one:

    def analyze_column(
    self, column: str, common_limit: int = 10, value_truncate=None, total_rows=None
    ) -> "ColumnDetails":

  2. simonw commented on Jan 7, 2022

    @simonw
    OwnerAuthor

    From the SQLite docs:

    If no arguments are given, all attached databases are analyzed. If a schema name is given as the argument, then all tables and indices in that one database are analyzed. If the argument is a table name, then only that table and the indices associated with that table are analyzed. If the argument is an index name, then only that one index is analyzed.

    So I think this becomes two methods:

    • db.analyze() calls analyze on the whole database
    • db.analyze(name_of_table_or_index) for a specific named table or index
    • table.analyze() is a shortcut for db.analyze(table.name)
  3. simonw commented on Jan 7, 2022

    @simonw
    OwnerAuthor

    Since the existing CLI feature is this:

    $ sqlite-utils analyze-tables github.db tags
    

    I can add sqlite-utils analyze to reflect the Python library method.

  4. simonw commented on Jan 8, 2022

    @simonw
    OwnerAuthor

    Running ANALYZE creates a new visible table called sqlite_stat1: https://www.sqlite.org/fileformat.html#the_sqlite_stat1_table

    This should be added to the default list of hidden tables in Datasette.

    It looks something like this:

    tbl idx stat
    _counts sqlite_autoindex__counts_1 5 1
    global-power-plants_fts_config global-power-plants_fts_config 1 1
    global-power-plants_fts_docsize 33643
    global-power-plants_fts_idx global-power-plants_fts_idx 199 40 1
    global-power-plants_fts_data 136
    global-power-plants "global-power-plants_owner" 33643 4
    global-power-plants "global-power-plants_country_long" 33643 202

    In each such row, the sqlite_stat.stat column will be a string consisting of a list of integers followed by zero or more arguments. The first integer in this list is the approximate number of rows in the index. (The number of rows in the index is the same as the number of rows in the table, except for partial indexes.) The second integer is the approximate number of rows in the index that have the same value in the first column of the index. The third integer is the number number of rows in the index that have the same value for the first two columns. The N-th integer (for N>1) is the estimated average number of rows in the index which have the same value for the first N-1 columns. For a K-column index, there will be K+1 integers in the stat column. If the index is unique, then the last integer will be 1.

  5. simonw commented on Jan 8, 2022

    @simonw
    OwnerAuthor

    Built a prototype in a branch, see #367.

  6. added this to the 3.21 milestone on Jan 10, 2022
  7. simonw commented on Jan 10, 2022

    @simonw
    OwnerAuthor

    I'm going to implement the Python library methods based on the prototype:

    commit 650f97a08f29a688c530e5f6c9eedc9269ed7bdc
    Author: Simon Willison <swillison@gmail.com>
    Date:   Sat Jan 8 13:34:01 2022 -0800
    
        Initial prototype of .analyze(), refs #366
    
    diff --git a/sqlite_utils/db.py b/sqlite_utils/db.py
    index dfc4723..1348b4a 100644
    --- a/sqlite_utils/db.py
    +++ b/sqlite_utils/db.py
    @@ -923,6 +923,13 @@ class Database:
             "Run a SQLite ``VACUUM`` against the database."
             self.execute("VACUUM;")
     
    +    def analyze(self, name=None):
    +        "Run ``ANALYZE`` against the entire database or a named table or index."
    +        sql = "ANALYZE"
    +        if name is not None:
    +            sql += " [{}]".format(name)
    +        self.execute(sql)
    +
     
     class Queryable:
         def exists(self) -> bool:
    @@ -2902,6 +2909,10 @@ class Table(Queryable):
                 )
             return self
     
    +    def analyze(self):
    +        "Run ANALYZE against this table"
    +        self.db.analyze(self.name)
    +
         def analyze_column(
             self, column: str, common_limit: int = 10, value_truncate=None, total_rows=None
         ) -> "ColumnDetails":
  8. simonw commented on Jan 10, 2022

    @simonw
    OwnerAuthor
  9. simonw commented on Jan 10, 2022

    @simonw
    OwnerAuthor

    With respect to #365, I'm now thinking that having the ability to say "... and then run ANALYZE" could be useful for a bunch of Python methods. For example:

    db["dogs"].insert_all(list_of_dogs, analyze=True)
    db["dogs"].create_index(["name"], analyze=True)
  10. simonw commented on Jan 10, 2022

    @simonw
    OwnerAuthor

    Having browsed the API reference I think the methods that would benefit from an analyze=True parameter are:

    • db.create_index
    • table.insert_all
    • table.upsert_all
    • table.delete_where
  11. simonw commented on Jan 11, 2022

    @simonw
    OwnerAuthor

    The Python methods are all done now, next step is the CLI options. I'll do those in a separate issue.

  12. changed the title [-]Utilities for calling ANALYZE[/-] [+]Python library methods for calling ANALYZE[/+] on Jan 11, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Projects

    No projects

      Milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions