SQLite ANALYZE

The ANALYZE command scans the database and collects statistics about the distribution of values in each indexed column. SQLite's query planner uses these statistics to choose the fastest execution plan. Without fresh statistics, the planner may pick a slower index or skip one entirely.

Why the Query Planner Needs Statistics

TABLE: orders (1 million rows)
  - 999,000 rows have status = 'delivered'
  -     500 rows have status = 'pending'
  -     500 rows have status = 'cancelled'

INDEX: idx_status ON orders(status)

QUERY: SELECT * FROM orders WHERE status = 'pending';

Without statistics:
  Planner doesn't know 'pending' is rare.
  It might do a full table scan.   ← slow

With ANALYZE:
  Planner knows 'pending' = 500 rows out of 1M.
  It uses the index — retrieves 500 rows directly. ← fast

Running ANALYZE

-- Analyze the entire database
ANALYZE;

-- Analyze one specific table
ANALYZE employees;

-- Analyze one specific index
ANALYZE idx_dept_salary;

Where Statistics Are Stored

-- ANALYZE writes results to internal tables:
SELECT * FROM sqlite_stat1;

tbl       │ idx             │ stat
──────────┼─────────────────┼──────────────────
employees │ idx_dept_salary  │ 5000 500 50
employees │ idx_email        │ 5000 1

Reading "5000 500 50" for idx_dept_salary(dept_id, salary):
  5000 → total rows in table
   500 → average rows matching a given dept_id value
    50 → average rows matching a dept_id + salary combination

The planner uses these numbers to estimate selectivity.

Verifying ANALYZE Ran Successfully

-- Check that stats exist for your table
SELECT tbl, idx, stat FROM sqlite_stat1
WHERE tbl = 'employees';

tbl       │ idx              │ stat
──────────┼──────────────────┼──────────────────
employees │ idx_email        │ 5000 1
employees │ idx_dept_salary  │ 5000 500 50

If this table is empty, ANALYZE has not been run yet.

ANALYZE + EXPLAIN QUERY PLAN Together

-- Before ANALYZE: planner might choose wrong index
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'pending' AND amount > 1000;

detail
────────────────────────────────────────────────────────────────
SEARCH orders USING INDEX idx_amount (amount>?)
-- Chose idx_amount even though status is highly selective

-- Run ANALYZE:
ANALYZE;

-- After ANALYZE: planner picks the better index
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE status = 'pending' AND amount > 1000;

detail
────────────────────────────────────────────────────────────────
SEARCH orders USING INDEX idx_status (status=?)

When to Run ANALYZE

RUN ANALYZE WHEN:
──────────────────────────────────────────────────────────
✔ After bulk INSERT, UPDATE, or DELETE operations
✔ After adding new indexes to an existing table
✔ After major data distribution changes
✔ Queries are slower than expected despite having indexes
✔ EXPLAIN QUERY PLAN shows unexpected index choices
✔ Scheduled maintenance (monthly or after large data loads)

ANALYZE is fast — it reads index pages, not all data rows.
On a table with 1M rows it typically runs in seconds.

Automatic Statistics (SQLite 3.38+)

-- Enable automatic statistics collection:
PRAGMA analysis_limit = 1000;

-- SQLite will automatically update stats for the
-- most important index entries during normal operations.
-- Set to 0 to disable (default in older SQLite).

ANALYZE vs VACUUM — Different Jobs

COMMAND     PURPOSE
────────────────────────────────────────────────────────────────
VACUUM      Reclaim disk space from deleted rows
ANALYZE     Update statistics for the query planner

They solve different problems. Run both during maintenance.
ANALYZE does not compact the file.
VACUUM does not update query statistics.

Practical Maintenance Script

-- Run after a major data operation:
PRAGMA optimize;   -- auto-analyze if stats are stale
ANALYZE;           -- force fresh statistics
VACUUM;            -- compact the file

-- Then verify:
SELECT tbl, stat FROM sqlite_stat1 ORDER BY tbl;
PRAGMA freelist_count;  -- should be 0 after VACUUM

Key Takeaways

• ANALYZE collects statistics about value distributions in indexed columns
• The query planner uses these statistics to choose the fastest execution plan
• Statistics are stored in sqlite_stat1 — query it to confirm ANALYZE ran
• Run ANALYZE after bulk data changes or after adding new indexes
• ANALYZE updates the plan; VACUUM reclaims space — they do different things