Giving the planner statistics in SQL

The planner guesses row counts. ANALYZE replaces the guess with a measurement.

ANALYZE;

SELECT
    tbl AS table_name,
    idx AS index_name,
    stat
FROM sqlite_stat1
ORDER BY tbl, idx;

How it works

1. ANALYZE writes row and index statistics into sqlite_stat1.
2. Re-run it after the data's shape changes, not after every write.
3. Reading sqlite_stat1 shows you what the planner now believes.

Keywords and builtins used here

• ANALYZE
• AS
• BY
• FROM
• ORDER
• SELECT
• table_name

The run, in numbers

Lines

8

Characters to type

96

Tokens

20

Three-star pace

90 tpm

At the three-star pace of 90 tokens a minute, this run takes about 13 seconds.

Type this snippet

Step 2 of 2 in Reading a plan, step 2 of 17 in Plans & performance.

← Previous Next →