Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Transactions and Performance

SQL query optimizer and statistics

Concept guide to the SQL query optimizer: cost-based planning, table statistics, ANALYZE, selectivity and why a plan changes when your data changes.

When you send a query, the database does not run it as written. SQL says what you want; the query optimizer decides how to get it: which index to use, which table to read first in a join, whether to sort or to use an index order, and which join method to apply. It enumerates candidate plans, estimates what each would cost, and picks the cheapest.

The estimates come from statistics: stored facts about your data such as row counts, the number of distinct values in a column and how values are distributed. If the statistics are stale or missing, the estimates are wrong and the optimizer can pick a bad plan even though your query and indexes are fine.

What the optimizer weighs

Question Why it matters
How many rows does each table have? Small tables are cheap to scan; big ones are not.
How selective is each condition? A condition that matches 1 row in 10,000 favors an index; one matching half the table does not.
Which indexes exist, and do they cover the query? A covering index avoids visiting the table at all.
How are joined tables related? The smaller filtered side usually drives a nested loop.
Is a sort needed, and does an index already provide the order? Avoids a temporary sort.

Selectivity is the fraction of rows a condition keeps. status = 'cancelled' might keep 3% (selective), status <> 'cancelled' keeps 97% (not selective).

Seeing the statistics in SQLite

SQLite stores statistics in a table called sqlite_stat1, filled by the ANALYZE command. Each row describes one index: the table, the index name and a string of numbers. The first number is the total number of rows; each next number is the average number of rows that share the same value of the first column, the first two columns, and so on.

SQL ANALYZE and sqlite_stat1 ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The table has 120 orders. For idx_orders_status the second number is 30: with four distinct statuses, an equality search on status matches about 30 rows on average. For idx_orders_customer it is 4: each customer has about four orders, so a customer search is much more selective. The optimizer uses these averages to estimate how many rows an equality search returns.

The plan follows the data

The same query can get a different plan when the facts about the data change. Here a condition on a very repetitive column is compared with a condition on a nearly unique one.

SQL Selective vs non-selective index ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In the first query two indexes could help, and the plan uses idx_orders_customer, the one it estimates to be more selective (about 4 rows against about 30). The second query only has the status condition, so it uses idx_orders_status. Without ANALYZE it uses default guesses; with statistics, it uses your real data. On a table this small the difference in speed is invisible, but on millions of rows a wrong guess means a different join order or a full scan.

Keeping statistics fresh

SQL
-- SQLite
ANALYZE;                -- all tables
ANALYZE orders;         -- one table
PRAGMA optimize;        -- let SQLite analyze only what looks stale

-- PostgreSQL
ANALYZE orders;         -- autovacuum also runs it automatically
VACUUM (ANALYZE) orders;

-- MySQL
ANALYZE TABLE orders;

-- SQL Server
UPDATE STATISTICS dbo.orders;   -- auto-update is on by default

-- Oracle
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');

Most server databases refresh statistics automatically after enough rows change; they can still lag behind after a bulk load or a big delete, so run the analyze step manually after those.

Dialect differences

Topic SQLite PostgreSQL MySQL SQL Server Oracle
Optimizer type Cost-based, simple Cost-based Cost-based Cost-based Cost-based
Statistics sqlite_stat1 (from ANALYZE) pg_stats, histograms, most common values Index cardinality, optional histograms Histograms per statistic Histograms via DBMS_STATS
Automatic refresh No (use PRAGMA optimize) Yes (autovacuum) Yes (persistent stats) Yes Yes (nightly job)
Show the plan EXPLAIN QUERY PLAN EXPLAIN (ANALYZE) EXPLAIN ANALYZE Execution plan EXPLAIN PLAN, DBMS_XPLAN
Hints INDEXED BY, NOT INDEXED Few (extensions) USE INDEX, FORCE INDEX OPTION (...), WITH (INDEX) /*+ ... */

Usage notes

  • Estimates, not guarantees. The optimizer works from averages; skewed data (one value covering 90% of rows) can fool simple statistics. Histograms and "most common values" help servers that keep them.
  • Compare estimated and actual rows. In PostgreSQL, MySQL and SQL Server plans with timing information, a large gap between the two is the usual sign of stale statistics.
  • Rewrite before you hint. Making a predicate sargable (sargable predicates) or adding the right index fixes more problems than forcing a plan.
  • Plans change with parameters. A query tuned for a rare value can be slow for a common one. Test with both.
  • Do not analyze constantly. It costs I/O; schedule it after large changes.
  • Tiny test data lies. A plan chosen for 50 rows can differ from the plan chosen for 50 million. Test on realistic volumes.