10,000
Rows in this index.
SQLite · Lesson 2
How estimates shape a plan
SQLite must choose an access path before it knows exactly how many rows the query will find. Statistics help it predict the work.
You need SCAN, SEARCH, and loop order from lesson 1. SQLite's ordinary plan output does not print numerical row estimates. This lesson uses data distributions and index statistics to investigate what SQLite knows about the data.
An average can hide large groups
For a table with 100,000 rows, predicting that 2% match means expecting 2,000 rows. The matching fraction is called selectivity. The planner needs statistics to choose a useful fraction.
Four values do not mean four equal groups
Each square represents 1% of a 100,000-row table. Choose a status; outlined squares are the matching rows.
If every value were equally common
25% means 25,000 rowsThis table's actual distribution
1% means 1,000 rowsAn uneven distribution is called skew. In a table with 10,000 orders and ten customers, the average is 1,000 orders per customer. One customer can still have 9,000 while the others have about 111 each.
Collect and inspect SQLite statistics
ANALYZE orders;
SELECT tbl, idx, stat FROM sqlite_stat1 WHERE tbl = 'orders';ANALYZE collects statistics about tables and indexes. It does not run your application SELECT or rebuild its indexes. On a large database it can do substantial work, so first use the disposable experiment.
-- Example sqlite_stat1 row for (customer_id, created_at):
-- tbl idx stat
-- orders orders_customer_date_idx 10000 1000 11,000
Average rows sharing the first key, customer_id.
1
Average rows sharing both customer_id and created_at.
Not a per-value count
The 1,000 average does not say that customer 1 has exactly 1,000 orders.
These numbers describe successive key prefixes in a normal multicolumn index. The stat string may contain additional tokens after them. Do not edit the statistics tables as a routine optimization technique.
Work backward from a prefix statistic
A key prefix means the first key, or the first two keys, and so on, in index order. For an index on customer_id and created_at, all entries sharing a customer form one first-prefix group. Entries sharing both that customer and an exact stored timestamp form a more specific group. A statistic about the second prefix concerns those pairs. It is not an average for created_at by itself across every customer.
Read the example statistic 10000 1000 1 in that order. Ten thousand entries divided by an average group size of one thousand suggests about ten distinct customer groups. Dividing ten thousand by an average pair-group size of one suggests about ten thousand distinct customer/timestamp groups. These are interpretations of summarized statistics, not permission to infer every individual group or a declared uniqueness constraint.
An average of one entry per pair does not replace a UNIQUE constraint. Statistics describe data collected at a particular time and may be approximate or rounded. A constraint governs which future writes are accepted. If correctness requires a customer/timestamp combination to be unique, enforce the intended rule in the schema rather than relying on a statistic observed today. Conversely, do not add a uniqueness rule merely to improve a plan if duplicate timestamps are valid business data.
Construct two distributions with the same total and customer count. In the first, ten customers each have one thousand orders. In the second, one customer has nine thousand and the other nine share the remaining thousand. Both have an average of one thousand orders per customer. An equality query for the large customer in the second distribution is very different from a query for a small customer, even though that first-prefix average is unchanged.
The status diagram supplies another example of unequal groups. Its equal-frequency reference divides one hundred thousand orders equally among four statuses, giving twenty-five thousand per status. Select paid and compare that reference with the actual ninety thousand paid orders. Then select pending, whose actual group has only one thousand. These differences show that an average does not determine an individual group's size. They do not reveal the planner's internal estimate for either value. STAT4 samples, other available information, and the query can affect the choice.
A useful investigation records both summaries and selected actual counts. Include a common value, a rare value, and a value with no rows. That selection tests different parts of the distribution without requiring a complete frequency table in every report. Keep the selected values relevant to the application. Testing only an artificially rare customer can make an index look more broadly beneficial than the workload supports.
More detail depends on the SQLite build
SELECT sqlite_compileoption_used('ENABLE_STAT4');A result of 1 means this build supports STAT4. ANALYZE can then store samples of index keys in sqlite_stat4. These describe the distribution in more detail than the averages in sqlite_stat1. A build without STAT4 has less information. Do not assume your application bundles the same SQLite build as your command-line shell.
In the companion experiment, sqlite_stat1 reports 10,000 rows and an average of 1,000 per customer. A direct count finds 9,000 for customer 1. This shows skew. It does not prove that SQLite predicted 1,000 rows for this query, since the planner may use additional statistics.
After data or schema changes
For modern SQLite, PRAGMA optimize; is the recommended way to maintain statistics. From SQLite 3.46 onward it limits the analysis work. It may analyze selected tables or do nothing. Running it does not guarantee that every statistic is refreshed.
PRAGMA optimize;Use targeted ANALYZE orders; for the controlled experiment here. For application maintenance, follow SQLite's connection-lifetime guidance in the linked documentation rather than running a full ANALYZE before every query.
Two filters may describe the same rows
SELECT id FROM customers
WHERE country = 'FR' AND currency = 'EUR';If each condition matches 20%, multiplying the fractions gives 4%. That multiplication assumes independence, meaning that matching one condition does not change the chance of matching the other. If every French customer in this dataset pays in euros, adding the currency condition removes none of them and the result is still 20%.
Related filters change the result
In a table of 100 customers, 20 are in France and 20 pay in euros. Change how many belong to both groups. Each group still has 20 customers.
Changing overlap resets the animation.
Independence estimate
0 rows
Actual matching rows
0 rows
Step 0 / 4
The estimate remains 100 × 0.2 × 0.2 = 4 rows. The actual result contains 4 rows, or 1 times that estimate. The bars count up for illustration; they do not show query execution.
This is a model of an independence assumption, not a reproduction of SQLite's estimator. A composite index has statistics for each leading group of keys. STAT4 can add samples. These help SQLite estimate combinations of conditions, but an index does not guarantee accurate estimates for every relationship between columns or every expression.
SELECT count(*) FROM customers WHERE country='FR';
SELECT count(*) FROM customers WHERE currency='EUR';
SELECT count(*) FROM customers
WHERE country='FR' AND currency='EUR';These counts test the relationship directly. Before adding an index, check whether its keys help SQLite find the rows the query needs. More detailed statistics alone may not justify the index.
Inspect overlapping groups
Calculate the second filter within the first group
Multiplication is not the problem in the country/currency example. The problem is which second fraction you multiply. Start with the fraction of all customers who match the country. Then multiply by the fraction of those customers who match the currency. That conditional fraction answers how much the second test removes from the group that survived the first. It need not equal the currency fraction in the whole table.
For a separate dataset of one thousand customers, suppose two hundred are in France. If all two hundred use euros, the conditional euro fraction within the French group is one. Multiplying two hundred by one leaves two hundred matching both conditions. If only fifty of the French customers use euros, the conditional fraction is one quarter and the result is fifty. The country count did not change; the overlap did.
This calculation also gives a sanity check. Adding another AND condition cannot increase the number of matching rows in this simple single-table filter. The overlap cannot exceed either individual group's size. If a calculation predicts more rows matching both than matching France alone, it has counted different populations, mixed units, or used the wrong fraction. Check the data definitions before blaming the optimizer.
In the interactive comparison, change the relationship between the conditions while keeping their individual fractions in view. Predict the overlap first. If the individual fractions stay the same but the overlap moves, you have isolated information that marginal counts do not contain. The word correlation names dependence here; the lesson is not computing a particular numerical correlation coefficient or claiming that one attribute causes the other.
A composite index can help because it organizes combinations of keys and can carry prefix statistics. It can also make a more selective lookup possible. Those are distinct benefits. Creating an index changes both the physical access options and the information available to the planner. If a later plan changes, do not claim that better statistics alone caused it without a comparison that separates those effects.
Apply the same reasoning to tenant and status. A global pending fraction can be small while almost every row of one recently imported tenant is pending. A test on a typical tenant then understates the candidate group for that tenant. Inspect the joint count for representative tenants and statuses. Ordinary EQP will still show the access strategy rather than print the conditional fraction SQLite used. The counts help you form an explanation, but they do not expose every internal decision.
Investigate the data behind an expensive loop
The same lookup repeats 100 times more often
● Input▧ Work✓ Match× Rejected◆ Result
One amber dot represents ten customer lookups. Both sides use the same scale.
Expected: 10 customers
•
10 × 20 orders = 200 joined rows
Actual: 1,000 customers
••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••••
1,000 × 20 orders = 20,000 joined rows
The diagram compares an assumed estimate with known data. These are teaching counts, not EXPLAIN QUERY PLAN output. Each lookup finds the expected number of orders, but there are 100 times as many lookups as expected.
- Measure the filter's actual count with a separate COUNT query in a test copy.
- Inspect available indexes, sqlite_stat1, and whether STAT4 is enabled.
- Check recent imports or changes in distribution. Record the plan before refreshing statistics.
- Refresh statistics deliberately, then compare the plan and repeated timings for the original query.
A COUNT query can use a different, covering plan from the original query. It tells you how many rows match, not how long the original query took or how many candidates it inspected. If ANALYZE changes the plan, measure again to check whether it helped.
Use the application's actual parameter values and types. Test customers with few orders and customers with many orders. Match the application's SQLite version, compile options, indexes, and statistics before treating a shell result as evidence of production behavior.
Explain repeated work
Trace how an early count changes later work
The propagation diagram expects ten selected customers and twenty matching orders per customer. That suggests two hundred order matches. The actual data instead contain one thousand selected customers, still with twenty orders each. The actual matches become twenty thousand. The per-customer order count was correct in this example; the error was in how many times that lookup was expected to run.
This multiplication explains why a later access can look reasonable in isolation and still dominate total work. A cheap lookup repeated one thousand times is a different workload from the same lookup repeated ten times. If a third table contributes three items per order, the actual output grows again to sixty thousand item matches. Keep each quantity attached to its stage rather than describing the final mismatch as a single mysterious bad estimate.
Use the propagation diagram to separate the number of outer rows from the work per lookup. Predict what happens if only one of those quantities changes. Doubling the selected customers doubles the number of subsequent order lookups in this simple model. Doubling orders per customer leaves the number of customer-triggered lookups unchanged but doubles the matching orders they supply. Both can double later work through different mechanisms.
After an import, first distinguish larger data from poorer information about the data. More qualifying rows can make the old query genuinely more expensive even if the chosen plan remains appropriate. Stale statistics can also make a previously sensible plan less suitable. Refreshing statistics addresses the information problem; it cannot remove rows that the query is required to return or create a missing access path.
For a controlled comparison, record the data snapshot, existing indexes, statistics, plan, and repeated timings before analysis. Then run the intended statistics maintenance in the test copy without changing the query or adding an index. Recheck the same values. A changed plan followed by lower timings supports the usefulness of that maintenance for the tested case. An unchanged plan is not proof that ANALYZE did nothing; the old route may still be preferred.
Keep the maintenance decision separate from the diagnosis. Running a full analysis before every request adds work and is not the recommended application pattern. PRAGMA optimize follows version-dependent guidance and may choose no analysis at all. If a query remains slow, investigate whether the workload needs different keys or whether most of the cost is unavoidable output. Better estimates help select among available alternatives, but they do not make an unsuitable set of indexes adequate for every query.
Exercises
Read an index statistic
An index on (tenant_id, created_at) has stat 60000 100 2. What do the numbers mean? Does tenant 7 necessarily have 100 rows?
Hint
The first number covers the whole index. Each later number adds one more key from the left.
Worked answer
Read the statistics for each key prefix
● Input▧ Work✓ Match× Rejected◆ Result
60,000 index rows
Tenant …Tenant …Tenant …Tenant …Tenant …Tenant …
One tenant prefix · average 100 rows
Jan 1● ●Jan 2● ●Jan 3● ●
One tenant + date prefix · average 2 rows
There are about 60,000 index rows, averaging 100 per tenant and 2 per tenant/date pair. This does not say that tenant 7 has exactly 100 rows. Count that tenant's rows and inspect available sampled statistics if skew matters.
An import changes the distribution
A query slowed after importing thousands of orders for one customer. Its customer index still exists. What would you investigate before adding another index?
Hint
Record the original plan and measurements before making a change.
Worked answer
An import can make the statistics stale
● Input▧ Work✓ Match× Rejected◆ Result
Before import · 200 orders
••
Statistics collected before import
After import · 2,000 orders
••••••••••••••••••••
Old statistics may be stale
Keep the imported data fixed. Run ANALYZE, then compare plans and timings.
Check how many rows now match, whether statistics are stale, and whether the application uses the same database and build as the test. Refresh statistics in a controlled copy and compare again. The plan may stay the same because the existing access path still suits the query.
Calculate the overlap between two filters
A country matches 10% of 50,000 customers. A currency matches 20%. Every customer in the country uses that currency. Compare the independence model with the actual result.
Hint
First multiply the fractions. Then count how many customers the second condition actually removes.
Worked answer
All 5,000 country matches already use this currency
● Input▧ Work✓ Match× Rejected◆ Result
50,000 customers in total
Currency · 10,000
40,000 outside the currency groupCountry · 5,000
● ● ● ● ●
All five dots match both filters● ● ● ● ●
Model: 5,000 × 20% = 1,000
Actual: all 5,000 also match the second filter.
The independence model predicts 50,000 × 0.1 × 0.2 = 1,000 rows. If all 5,000 customers matching the first condition also match the second, the actual result is 5,000. Ordinary SQLite EQP does not reveal whether the planner used that independence model.
Experiment and sources
Run sqlite3 estimates.db < estimates.sql with a new file and the statistics experiment. It creates skewed data, inspects sqlite_stat1, reports STAT4 support, and prints plans before and after ANALYZE.
Next: how those inputs affect join order and repeated lookups.