HardSQL#1427 min readJul 11, 2026
Statistics
practice
Practice modeTest yourself instead of reading straight through
Hook: Indexes are the road signs; statistics are the traffic report that tells the database which road is fast right now.
Question: What are statistics in SQL databases, and why do they matter for index performance?
Answer: Statistics are small pieces of metadata about table data, such as how many rows exist, how many values are distinct, which values are common, and how the values are spread out. The query planner uses them to estimate cardinality meaning the number of rows a query is likely to touch. Those estimates help it choose between a sequential scan, an index scan, and different join orders.
Interview-Ready Answer: In SQL databases, statistics are the optimizer’s facts about the data distribution. I think of them as the database’s “guessing engine” inputs: the planner uses them to estimate how many rows match a filter, which index is worth using, and which join order will be cheapest. They do not speed up a query directly; instead, they help the database pick a better plan. A big gotcha is stale statistics: if the data changes a lot and ANALYZE has not run, the optimizer can make a very bad choice even when the right index exists.
%22%20stroke%3D%22%234f46e5%22%20stroke-width%3D%221.5%22%2F%3E%3Ctext%20x%3D%22500%22%20y%3D%22308%22%20text-anchor%3D%22middle%22%20font-size%3D%2215%22%20font-weight%3D%22bold%22%20fill%3D%22%23ffffff%22%20font-family%3D%22Segoe%20UI%2C%20Arial%2C%20sans-serif%22%3ESQL%20Stats%3C%2Ftext%3E%3C%2Fsvg%3E)
What statistics really are
Detailed Explanation: Statistics are not the same thing as indexes. An index is a data structure that helps the engine find rows quickly; statistics are facts about the data that help the engine predict how expensive a plan will be. In PostgreSQL, for example, the planner reads column statistics such as null_frac, n_distinct, most_common_vals, histogram bounds, and correlation. Other databases store similar ideas under different names.
How the optimizer uses them
- The table changes. Inserts, updates, and deletes slowly make the old picture of the data less accurate.
ANALYZEor auto-analyze collects fresh facts. Most systems sample the table rather than scanning every row, so this is much cheaper than a full read of the table. In PostgreSQL, the default statistics target is100, and auto-analyze is usually triggered after roughly50 + 10%of a table has changed, though exact behavior is database-specific.- The engine records distributions. It learns things like: 90% of rows have status
open, 1% haveclosed, and values are spread mostly within a certain range. - The planner estimates selectivity. Selectivity means the fraction of rows that match a filter. If the planner thinks a predicate is very selective, it may choose an index. If it thinks many rows match, a sequential scan may be cheaper.
- The planner estimates cardinality. Cardinality is the expected row count after a filter or join. This estimate drives join order, scan type, and whether to use a nested loop, hash join, or merge join.
- The best-looking plan is chosen. The optimizer compares estimated costs and picks the lowest one. If the stats are wrong, the cost model is still “doing its job” but with bad inputs.
Statistics vs indexes
| Topic | Statistics | Indexes |
|---|---|---|
| Purpose | Estimate rows | Find rows fast |
| Main use | Plan choice | Execution speed |
| Built from | Samples/distributions | Key values and pointers |
| Maintenance cost | Low to medium | Write overhead |
| Bad outcome | Wrong plan | Slower writes, storage use |
Why stale statistics hurt
If a table used to be 99% open and suddenly becomes 30% closed, the planner may still believe closed is rare. It then may pick an index scan that touches far more rows than expected, or choose the wrong join order. This is why large batch jobs, bulk loads, or sudden traffic shifts often need a manual ANALYZE afterward.
Important edge cases
- Correlated columns: if
countryandcurrencymove together, single-column stats may assume they are independent and undercount the result set. Some databases support extended statistics for this. - Small tables: a sequential scan can still be faster than an index lookup, even if the predicate seems selective. The table may fit in memory, so the overhead of random index lookups is not worth it.
- Stale-but-correct: stale stats do not change query results, only the path the engine takes to get them. The danger is latency, CPU, and I/O blow-ups.
Memory angle: The planner is a detective; statistics are its witness statements. If the witnesses are old or misleading, the detective picks the wrong route.
Real-World Example: Imagine an e-commerce checkout service that queries orders for pending payments. During a flash sale, a background job flips huge numbers of rows to pending. For a while, the planner still thinks pending orders are rare, so it chooses a plan that looks cheap on paper but actually touches tens of thousands of rows. The symptom is a sudden jump in P95 latency, CPU spikes on the database, and logs that show estimates like rows=50 when the query actually returns rows=20000.
The fix is usually to refresh stats with ANALYZE, let auto-analyze catch up, and sometimes add extended statistics or a better index if the access pattern is stable. The important lesson is that a good index can still underperform if the optimizer is making decisions with stale information.
SQL
-- PostgreSQL demo: statistics help the planner estimate row counts.
-- This script shows three stages:
-- 1) fresh table with no stats yet
-- 2) analyzed table with useful stats
-- 3) stale stats after the data distribution changes
DROP TABLE IF EXISTS order_events;
CREATE TEMP TABLE order_events (
order_id bigserial PRIMARY KEY,
status text NOT NULL,
amount numeric(10,2) NOT NULL
);
-- Build a skewed dataset:
-- Most rows are 'open', a small slice are 'closed'.
INSERT INTO order_events (status, amount)
SELECT CASE WHEN gs <= 49500 THEN 'open' ELSE 'closed' END,
CASE WHEN gs % 100 = 0 THEN 999.99 ELSE 19.99 END
FROM generate_series(1, 50000) AS gs;
CREATE INDEX idx_order_events_status ON order_events(status);
-- Before ANALYZE, the optimizer has little distribution information.
-- The exact plan can vary by PostgreSQL version and settings.
EXPLAIN
SELECT *
FROM order_events
WHERE status = 'closed';
-- Refresh column statistics.
ANALYZE order_events;
-- Inspect the collected statistics.
SELECT attname,
null_frac,
n_distinct,
most_common_vals,
most_common_freqs,
histogram_bounds,
correlation
FROM pg_stats
WHERE schemaname LIKE 'pg_temp%'
AND tablename = 'order_events'
ORDER BY attname;
-- After ANALYZE, the planner has a better estimate for the skewed status column.
EXPLAIN
SELECT *
FROM order_events
WHERE status = 'closed';
-- Failure path: a large data shift happens, but stats are now stale.
-- Now 'closed' is no longer rare, so the old estimate is misleading.
INSERT INTO order_events (status, amount)
SELECT 'closed', 42.00
FROM generate_series(1, 20000);
-- This plan may still reflect the old, too-optimistic picture.
EXPLAIN
SELECT *
FROM order_events
WHERE status = 'closed';
-- Fix the stale stats so the optimizer can re-estimate correctly.
ANALYZE order_events;
EXPLAIN
SELECT *
FROM order_events
WHERE status = 'closed';Follow-up & Tricky Questions:
- How is
ANALYZEdifferent fromVACUUM?ANALYZEcollects planner statistics.VACUUMreclaims dead tuples and keeps storage healthy. They are related, but they solve different problems. - What is cardinality? It is the estimated number of rows a predicate or join will produce. Good cardinality estimates are the foundation of good plans.
- What does the database store in statistics? Common values, frequencies, null rate, value spread, and sometimes correlation or multi-column dependencies. These facts help estimate selectivity.
- When do stats get refreshed automatically? Most engines refresh them after enough data changes, usually via autovacuum or a background task. The exact threshold and schedule depend on the database and configuration.
- What are extended statistics? They are extra stats for correlated columns or distinct counts across multiple columns. They help when single-column stats assume independence and get the estimate wrong.
- Does adding an index automatically fix bad statistics? No. An index can give the optimizer a new access path, but without good stats it may still choose the wrong path.
- Do stale statistics change query results? No, they only affect performance. The danger is a slow or expensive plan, not incorrect data.
- Can a query still use a sequential scan with a good index? Yes. If the filter matches a large part of the table, the planner may correctly decide the index is not worth it.
Tricky / gotcha questions:
- Does a unique index remove the need for statistics? No. Uniqueness helps the optimizer know one key is unique, but it still needs overall distribution information for other predicates and joins.
- If the plan uses an index, does that mean stats were perfect? No. The plan may simply be the best of several imperfect estimates. Good plans can come from approximate stats.
- If a table is small, are statistics irrelevant? No. Small tables often still use stats, but the engine may prefer a sequential scan because the whole table is cheap to read anyway.
Common Mistakes:
- Thinking statistics are the same as indexes. Correction: indexes speed lookup; statistics guide the planner.
- Forgetting to refresh stats after bulk loads. Correction: run
ANALYZEor let auto-analyze catch up before judging performance. - Assuming the most selective-looking filter always uses an index. Correction: the planner compares total cost, not just selectivity.
- Ignoring correlated columns. Correction: if columns move together, consider extended statistics or a different schema/index design.
Memory Hook: “Index = shortcut. Statistics = live traffic.” The shortcut only helps if the traffic report is current.
Cheat Sheet:
- Statistics are metadata about data distribution.
- The planner uses them to estimate selectivity and cardinality.
- Good stats improve plan choice; they do not speed queries directly.
- Stale stats cause wrong join orders and bad scan choices.
ANALYZErefreshes stats; auto-analyze usually does it in the background.- Skewed or correlated data may need extended statistics.
Practice Tasks:
- Create a skewed table, add an index, and compare
EXPLAINbefore and afterANALYZE. - Insert a large batch that changes the distribution, then observe how the plan can become stale.
- Query your database’s stats view, such as
pg_stats, and identify the most common values and histogram fields.