Database Statistics in SQL

Introduction

SQL is declarative: a query states which result is required, while the database optimizer decides how to produce it. To compare a table scan, an index access path, alternative join orders, different join algorithms, parallel execution, and memory requirements, the optimizer needs an informed estimate of how much data each operation will process. Database statistics provide that information.

Statistics are compact metadata describing table size, column values, distinct counts, null frequency, ranges, distributions, and sometimes relationships between columns. They do not contain every row and they do not guarantee a perfect prediction. Their purpose is to give the optimizer a useful model of the data. When the model reflects reality, row estimates and plan choices are more likely to be appropriate. When it is stale or incomplete, a syntactically correct query may receive a poor plan.

What Are Database Statistics?

Database statistics are information collected by a database management system about the contents of tables, indexes, columns, or partitions. A statistics object may include an approximate row count, the number of distinct values, the fraction of nulls, common values, data ranges, average row or value width, and histogram buckets. The exact fields and storage differ by database engine.

Consider a table containing one million employees. If 700,000 belong to department 10 and only 10,000 belong to department 40, the same equality predicate has very different selectivity for those values. A scan may be reasonable for the first value, while targeted index access may be attractive for the second. Statistics give the optimizer evidence for that decision.

Why Statistics Are Important

The optimizer evaluates candidate plans through a cost model. Estimated row counts influence nearly every part of that comparison: access paths, join order, join type, sort and hash memory, parallelism, and whether intermediate results should be materialized. An error early in a plan can propagate through all later calculations.

Accurate statistics cannot make every query fast, because SQL design, indexes, hardware, concurrency, and configuration also matter. They do, however, help the optimizer avoid plans based on unrealistic assumptions. Statistics maintenance is therefore a core performance responsibility, especially after rapid growth, bulk changes, unusual data loads, or shifts in value distribution.

A Library Analogy

Imagine a library with one million books. Its catalog is comparable to an index because it identifies where particular books are located. Statistics are more like the librarian's knowledge that 70 percent of books are technology titles, 20 percent are science, and only one percent are geography. That distribution helps the librarian decide whether using a catalog lookup or walking through a large section is more efficient.

The catalog and the distribution knowledge serve different purposes. In the same way, an index provides a physical or logical access structure, while statistics help the optimizer judge whether using that structure is worthwhile for a specific query.

How Statistics Participate in Optimization

After SQL is parsed and validated, the optimizer identifies legal execution strategies. It consults available statistics to estimate the rows produced by filters, joins, and groups. Those cardinality estimates feed estimates for CPU, I/O, memory, and communication. The optimizer then selects a plan according to its cost model and available features.

Statistics are not consulted in isolation. Query structure, indexes, constraints, partitioning, physical storage, parameter values, cached plans, optimizer settings, and engine-specific rules all affect the result. A good investigation therefore connects statistics to the complete execution plan rather than assuming that refreshing statistics is a universal repair.

Sample Database

The examples use a simple employee database that can represent skew, nulls, ranges, joins, and correlated columns.

CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name VARCHAR(100) NOT NULL,
  department_id INTEGER,
  salary DECIMAL(10, 2),
  city VARCHAR(100),
  joining_date DATE,
  employment_status VARCHAR(20),
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Assume most technology employees are in Chicago, salaries vary by department, and a large majority of rows are active. Those relationships create estimation challenges that single-column distributions may not fully describe.

Major Types of Statistics

StatisticWhat it describesWhy it matters
Row countApproximate table or partition sizeEstablishes the input scale
Distinct valuesNumber of different valuesSupports equality selectivity
HistogramDistribution across values or rangesModels skew and ranges
Null fractionShare of rows containing nullEstimates null predicates
Minimum and maximumObserved range boundariesSupports range reasoning
Multicolumn dataRelationships among columnsCorrects independence assumptions

Table-Level Statistics

Table-level information commonly includes row counts, page counts, or object size. These values establish the scale of an operation. A plan for a table with one hundred rows may reasonably scan it, while the same predicate on one hundred million rows requires closer access-path analysis. Partitioned systems may maintain both global and partition-level information.

Counts are often estimates because exact counting on every change would be expensive. Some engines scale stored values using current physical size or modification information. The optimizer needs a useful planning estimate, not necessarily a transactionally exact reporting count.

Column-Level Statistics

Column statistics describe the values used in filters, joins, grouping, and ordering. They may track distinct counts, nulls, common values, frequencies, ranges, correlation, density, or average width. A statistics object usually summarizes values rather than listing every one.

Columns used frequently in selective predicates often benefit most from accurate statistics. Statistics on columns never referenced by important queries may add collection and storage cost without meaningful benefit. Automatic collection policies differ, so administrators should understand which columns their platform analyzes by default.

Distinct Values and Density

The number of distinct values helps estimate equality predicates. If one million rows contain one thousand uniformly distributed department codes, a simple model might estimate roughly one thousand rows for one code. Real data is rarely perfectly uniform, so density is only part of the picture.

Distinct counts also affect joins and grouping. Joining two columns with different uniqueness characteristics produces a different row count than joining two unique keys. Constraints can provide additional certainty, but the optimizer still needs distribution information for nonunique values.

Data Distribution and Skew

Skew occurs when some values appear much more frequently than others. Status columns often contain mostly active rows and few inactive rows. Geographic data may be concentrated in a few cities, and multitenant systems may have one customer far larger than the rest. An average-density estimate can be seriously wrong for both common and rare values.

Skew explains why the same statement can behave differently with different parameters. The optimizer needs a way to recognize significant values or ranges, which is one reason database engines maintain common-value lists, histograms, or related structures.

Histograms

A histogram divides a column's observed distribution into a limited number of buckets. Depending on the engine and data, a bucket may represent one value, a range, or approximately equal portions of the population. Histograms compress a potentially enormous distribution into metadata small enough to collect and consult during optimization.

They are especially useful when data is not uniform. A salary histogram can show that most employees fall within a middle range while relatively few exceed a high threshold. A query using salary > 150000 can then receive a better estimate than one based only on minimum, maximum, and distinct count.

Histogram Buckets and Resolution

More buckets can represent a distribution in greater detail, but statistics collection takes more time, uses more metadata, and may still miss rapidly changing patterns. Too few buckets can merge important values into broad ranges. Engines choose defaults and often provide a target or bucket-count control for selected columns.

Increasing resolution everywhere is rarely sensible. Focus on columns where estimates are demonstrably poor and where better estimates can change an important plan. Verify the result after collection because a more detailed histogram does not guarantee a different or faster plan.

Null Statistics

Null represents missing or unknown information and follows special SQL semantics. Statistics commonly track the fraction or count of null values so predicates such as IS NULL and IS NOT NULL can be estimated. A nullable column with very few nulls may support selective access for an exception report, while a mostly null column behaves differently.

Nulls also affect joins, aggregates, and uniqueness rules. Troubleshooting should verify both the estimated null fraction and the query's intended treatment of missing data.

Minimum, Maximum, and Range Estimates

Observed boundaries help the optimizer reason about range predicates. If all recorded dates are between 2020 and 2026, a condition before 2010 should return no rows. Values beyond the known maximum can be difficult after rapidly increasing loads because newly inserted data may not yet be represented.

Range estimates depend on more than endpoints. Distribution inside the range matters, especially for dates with seasonal traffic or numeric values clustered around business thresholds.

Selectivity

Selectivity describes the fraction of rows expected to satisfy a condition. A highly selective predicate returns a small fraction, while a low-selectivity predicate returns much of the table. Selectivity influences whether targeted index access is likely to cost less than scanning.

SELECT employee_id, employee_name
FROM employees
WHERE employment_status = 'INACTIVE';

If only one percent of employees are inactive, this predicate is selective. If 90 percent are inactive, index access followed by many row lookups may be less efficient than a scan. Statistics allow the optimizer to distinguish those cases.

Cardinality Estimation

Cardinality estimation predicts how many rows flow from each plan operation. The estimate begins with base-table statistics and is transformed through filters, joins, grouping, set operations, and limits. These counts influence estimated CPU, I/O, memory, and data movement.

Because later estimates depend on earlier ones, find the first significant divergence between estimated and actual rows. A large error after several joins may originate from one initial filter, correlation assumption, or parameter value rather than from the final operator.

Estimated Rows vs Actual Rows

Estimated rows come from the optimizer's model at planning time. Actual rows are observed during execution when the engine and plan format provide runtime information. A small difference is normal because statistics are summaries. Orders-of-magnitude differences deserve investigation, particularly when they change access paths, joins, memory grants, or parallel behavior.

Compare representative executions. A plan may estimate one parameter accurately and another poorly because of skew or plan reuse. Runtime plans can add overhead and execute data-changing statements, so capture them safely.

How Statistics Affect Index Selection

An index is attractive when its navigation and row-fetch costs are expected to be lower than scanning. Statistics provide the estimated number of matching entries and subsequent lookups. Rare values may favor an index, while common values may favor sequential access.

Refreshing statistics can cause the optimizer to stop using an index, and that is not automatically a regression. If the data distribution changed, a scan may now be cheaper. Judge the decision with runtime evidence, not an assumption that an index must always be used.

How Statistics Affect Join Selection

Join order determines which intermediate sets are produced first, and join algorithms respond differently to input size and ordering. A small outer input with efficient inner access may suit nested loops. Larger equality joins may suit hashing, while ordered inputs may support a merge strategy. Statistics influence the row counts behind all of these choices.

Incorrect join estimates can multiply quickly. If the optimizer expects ten rows but receives one million, repeated inner access or an undersized hash operation can become expensive. Validate relationship cardinality, uniqueness, and column correlations alongside the statistics.

How Statistics Affect Memory and Parallelism

Sorts and hashes require working memory based partly on estimated rows and row width. Underestimates can contribute to temporary-storage spills; overestimates can reserve excessive memory and reduce concurrency. Database engines implement memory allocation differently, but cardinality remains a major planning input.

Estimated workload size can also influence parallel execution. A query believed to be small may remain serial, while a costly plan may receive parallel workers. Better statistics help the optimizer compare startup, coordination, and processing costs more realistically.

Automatic Statistics Collection

Modern database systems usually collect or refresh some statistics automatically. Triggers may include object creation, data modifications, background maintenance, or query compilation. Automatic behavior reduces administrative burden but does not eliminate the need for monitoring. Thresholds, timing, sampling, and eligible objects differ among engines.

Automatic collection can also create latency if a query waits for synchronous maintenance, while asynchronous modes may temporarily compile against older information. Administrators should understand the platform defaults before replacing them with broad scheduled jobs.

Manual Statistics Updates

Manual updates are useful after a bulk load, major delete, distribution shift, or proven estimate problem that automatic maintenance has not yet addressed. They also support controlled testing. Because updates consume CPU and I/O and can cause plan recompilation, refreshing everything as frequently as possible is not a sound policy.

Target the table, partition, index, or column involved, choose an appropriate sample, and observe collection duration. Afterward, compare estimates and plan performance. If no meaningful behavior changes, statistics may not have been the root cause.

Statistics Sampling

Sampling reads a subset of rows to estimate the full distribution. It lowers maintenance cost for large objects but introduces sampling error. Uniform data often needs less detail, while highly skewed or rare values may be missed by a small sample. Random samples can also produce slightly different metadata between collections.

Sampling percentage should be chosen from evidence. A fixed percentage may become increasingly expensive as a table grows, while a tiny sample may become unstable. Some engines use adaptive algorithms or allow a persisted sampling choice.

Full-Scan Collection

A full scan reads all eligible rows and can produce more complete statistics, but it requires more time, I/O, and maintenance capacity. It is appropriate for selected critical objects when sampling demonstrably causes poor estimates, not as an automatic default for every large table.

Schedule expensive collection with awareness of production workload, locking behavior, replicas, backups, and plan recompilation. Test whether the additional accuracy actually improves important queries.

PostgreSQL Statistics

PostgreSQL ANALYZE collects planner statistics, and autovacuum normally performs automatic analysis according to configured thresholds. Planner information is represented through catalog data and exposed in views such as pg_stats. Common fields include null fraction, distinct estimates, most common values, frequencies, histogram bounds, and correlation.

ANALYZE employees;

SELECT attname, null_frac, n_distinct, most_common_vals
FROM pg_stats
WHERE tablename = 'employees';

The planner combines this information with current relation size. Runtime plan comparison with EXPLAIN (ANALYZE, BUFFERS) can show whether collected statistics produce realistic row estimates.

PostgreSQL Statistics Targets

PostgreSQL allows the statistics target to be increased for selected columns. A higher target generally increases sample size and the detail available for common values and histograms, with additional analysis time and metadata. It is most useful for important skewed columns where default detail causes a proven estimate problem.

ALTER TABLE employees
  ALTER COLUMN department_id SET STATISTICS 500;

ANALYZE employees;

PostgreSQL Extended Statistics

Ordinary per-column statistics may assume that predicates are independent. PostgreSQL extended statistics can describe selected column combinations through dependencies, multivariate distinct counts, or most-common-value information. They are helpful when columns such as department and city are correlated.

Creating a statistics object records which combination matters; ANALYZE performs the collection. Extended statistics should target real query patterns because collecting every possible combination is impractical.

MySQL Statistics and Histograms

MySQL uses table and index information, including InnoDB persistent optimizer statistics, when estimating access and join choices. ANALYZE TABLE can refresh key distribution information. Cardinality can be inspected through index metadata, but it is an estimate rather than an exact distinct count.

MySQL also supports column histograms managed with ANALYZE TABLE. Histogram information is exposed through INFORMATION_SCHEMA.COLUMN_STATISTICS. Histograms are especially useful for nonindexed columns with skewed values, while indexed equality estimates may use index access information instead.

ANALYZE TABLE employees;

ANALYZE TABLE employees
  UPDATE HISTOGRAM ON department_id WITH 128 BUCKETS;

SQL Server Statistics

SQL Server creates statistics for index keys and can automatically create single-column statistics for query predicates when database options permit. A statistics object contains a header, density information, and a histogram on its leading column. Catalog views expose objects and columns, while management functions expose modification information.

AUTO_UPDATE_STATISTICS lets the optimizer determine when referenced statistics may be stale. Manual UPDATE STATISTICS or sp_updatestats can be appropriate in specific cases, but Microsoft recommends avoiding unnecessarily frequent updates because collection and recompilation have costs.

UPDATE STATISTICS dbo.employees;

CREATE STATISTICS st_employees_department
ON dbo.employees (department_id);

Oracle Optimizer Statistics

Oracle optimizer statistics describe tables, columns, indexes, and system characteristics. Automatic maintenance normally gathers missing or stale information, while the DBMS_STATS package supports controlled collection. Histograms can represent skewed columns, and preferences govern sampling, method options, and other behavior.

Statistics operations should follow Oracle's supported maintenance approach rather than relying on ad hoc manual calculations. Pending statistics, history, locking, incremental partition features, and dynamic sampling can matter in advanced environments, so changes require platform-specific testing and operational review.

Statistics vs Indexes

An index is an access structure maintained as table data changes. Statistics are descriptive metadata used for estimation. An index may have associated statistics, but the concepts are not interchangeable. Creating statistics does not provide a new path to rows, and creating an index does not guarantee that its distribution information will remain ideal for every query.

This distinction prevents two common mistakes: adding an index when the real issue is a bad estimate, and refreshing statistics when the query lacks any suitable access path.

Statistics vs Execution Plans

Statistics are inputs to planning; an execution plan is the selected strategy. Looking only at statistics does not reveal the chosen operators, while looking only at a plan may not explain why estimates were produced. Effective troubleshooting compares the two: identify the first row-estimate error, then inspect the statistics, predicates, constraints, parameters, and relationships that informed it.

What Makes Statistics Outdated?

Inserts, updates, deletes, merges, bulk loads, archival jobs, partition changes, and evolving business behavior can make stored distributions differ from current data. Row count may remain similar while values shift dramatically, so a modification threshold cannot detect every meaningful distribution change.

Stale statistics are a possibility, not a diagnosis. Symptoms include sudden plan changes, large estimated-versus-actual row differences, unstable access paths, or regressions following major data movement. Confirm the relevant object and distribution before collecting broadly.

Multicolumn Statistics and Correlation

Suppose most employees in department 10 are in Chicago. Independent single-column estimates may multiply the overall fraction for department 10 by the overall fraction for Chicago, even though the values are related. This can under- or overestimate combined predicates substantially.

Engines address correlation differently through extended statistics, multicolumn objects, index statistics, groups, or specialized optimizer features. Use the platform-supported mechanism when an important query has correlated predicates and the plan shows a resulting estimate problem.

Statistics and Parameter-Sensitive Plans

Statistics can reveal that values have different frequencies, but plan caching may still reuse one plan for multiple parameters. A plan optimized for a rare department may perform poorly for the dominant department. Test representative common, rare, null, and boundary values when evaluating estimates.

Solutions are engine-specific and may include adaptive plan features, recompilation, query variants, or statement redesign. Refreshing statistics can change the selected plan but does not remove the underlying parameter sensitivity.

Statistics and Plan Regressions

A statistics update can trigger recompilation and a new plan. The new plan may be better, worse, or simply different because the optimizer has a changed model. Preserve plan and performance history so regressions can be correlated with maintenance events. Avoid concluding that statistics are harmful merely because a plan changed; determine whether the new estimates reflect reality and why the resulting cost comparison failed.

Bulk Loads and Maintenance Timing

Bulk loads can change row count and distribution faster than automatic maintenance reacts. Collecting appropriate statistics after a major load can prevent the first production query from compiling with obsolete information or waiting for synchronous work. The same principle applies after large deletes, data migrations, or partition exchanges.

Coordinate collection with data validation, index maintenance, deployment, and workload schedules. Avoid duplicate work: some index rebuild operations also refresh related statistics, while reorganizations may not. The details vary by engine.

Temporary Tables

Temporary tables can contain anything from a few rows to millions, and their statistics behavior differs by platform. Some engines create and update statistics automatically; others rely on analysis, indexes, or limited assumptions. A plan compiled before a temporary table is populated may use an unrealistic size estimate.

When a temporary table drives an important join, inspect its actual rows, compilation timing, indexes, and available statistics. Replacing every temporary table with a common table expression does not automatically solve the issue because materialization and estimation semantics differ.

Partitioned Tables

Partitioned objects may have local, global, or incremental statistics. Queries that eliminate partitions need accurate partition boundaries and representative information within the remaining data. A newly loaded partition can have a distribution unlike older partitions, while global information may lag behind.

Choose maintenance that matches the engine's partition strategy and workload. Reanalyzing an entire very large table after every partition load may be unnecessary, but relying only on one local partition may misestimate cross-partition queries.

Real-World Example: Product Catalog

An online catalog contains mostly active products, but one category holds half the inventory. A query filtering active products by category is fast for small categories and slow for the dominant one. The plan was compiled using an average estimate. Better distribution information reveals the skew, but the application still needs a plan strategy appropriate for parameter sensitivity and pagination.

Real-World Example: Banking Transactions

A transaction table grows daily and is partitioned by date. Recent partitions contain far more mobile payments than historical partitions. A report over the newest week receives a poor estimate based on older global patterns. Partition-aware maintenance after loading and representative date testing restore better estimates while avoiding a full scan of all history.

Real-World Example: Testing Dashboard

A quality dashboard joins test runs and results using project, status, and execution date. Failed status is rare overall but common for one unstable project. Independent column estimates understate the combination, leading to an undersized hash operation and temporary spill. Multicolumn statistics or a query-specific design can represent the relationship more accurately.

Real-World Example: Employee Reporting

A salary report filters department and city, then groups by employment status. Department and city are strongly correlated. After data migration, the city distribution changes but scheduled collection has not run. Comparing estimated and actual rows identifies the first error, and targeted analysis corrects the model without adding an unnecessary index.

Common Statistics Mistakes

  • Assuming statistics and indexes are the same object or solve the same problem.
  • Refreshing every statistic whenever a query is slow.
  • Using full scans everywhere without measuring maintenance cost or benefit.
  • Ignoring skew and testing only an average parameter value.
  • Looking at a large estimate error only at the root instead of finding its origin.
  • Collecting single-column statistics when the actual problem is column correlation.
  • Changing statistics targets globally instead of focusing on important columns.
  • Forgetting that a statistics update may cause plan recompilation.
  • Ignoring temporary and partitioned objects.
  • Declaring success without comparing runtime plans and workload metrics.

Best Practices

Keep automatic statistics maintenance enabled unless a tested operational design requires otherwise. Monitor estimate quality on critical queries, preserve plan history, and align bulk-data workflows with targeted maintenance. Increase detail only for columns or combinations where the default model demonstrably fails.

Use representative parameters and production-like distributions in testing. Measure collection duration, query compilation, plan changes, CPU, reads, spills, and concurrency before and after maintenance. Document special statistics objects, targets, locks, and scheduled jobs so future teams understand why they exist.

Interview Questions and Answers

What are database statistics?

They are optimizer metadata describing table size and value characteristics such as distinct counts, null frequency, common values, ranges, and distributions.

Do statistics speed up data access directly?

No. They help the optimizer choose a plan. Indexes and storage structures provide access paths.

What is cardinality estimation?

It is the prediction of how many rows each execution-plan operation will produce or process.

Why can stale statistics cause a slow query?

They can produce inaccurate row estimates, leading to unsuitable access paths, joins, memory allocations, or parallelism decisions.

When are multicolumn statistics useful?

They are useful when important predicates use correlated columns and independent single-column estimates are inaccurate.

Practical Exercises

  1. Create a skewed department distribution and compare plans for common and rare values.
  2. Capture estimated and actual rows before and after statistics collection.
  3. Inspect distinct counts, null fractions, and common values using your database catalog.
  4. Compare sampled and full-scan collection on a safe test table.
  5. Change a large percentage of rows and observe when automatic maintenance occurs.
  6. Test correlated department and city predicates with and without multicolumn information.
  7. Measure plan and runtime changes after a bulk load.
  8. Document maintenance cost as well as query benefit.

Statistics Commands Quick Reference

PlatformCommon operationPurpose
PostgreSQLANALYZE table_nameCollect planner statistics
MySQLANALYZE TABLE table_nameRefresh key distribution information
SQL ServerUPDATE STATISTICS table_nameUpdate optimization statistics
OracleDBMS_STATS proceduresGather optimizer statistics

These are starting points, not interchangeable commands. Privileges, sampling, locking, logging, partition behavior, syntax, and operational impact vary by platform and version.

Statistics Troubleshooting Workflow

  1. Define the affected query, parameters, workload, and performance symptom.
  2. Capture the runtime plan and compare estimated with actual rows.
  3. Locate the first material cardinality error in the row flow.
  4. Identify the columns, expressions, joins, or partitions behind that estimate.
  5. Inspect available statistics, collection time, modification level, sample, and distribution.
  6. Check for skew, null concentration, ascending values, and column correlation.
  7. Confirm whether automatic maintenance should already have responded.
  8. Choose a targeted update, higher detail, histogram, or multicolumn object when justified.
  9. Collect safely and account for I/O, locking, recompilation, and maintenance windows.
  10. Retest representative common, rare, null, and boundary parameters.
  11. Verify plan stability, runtime improvement, and effects on concurrent work.
  12. Document the evidence and continue monitoring after deployment.

Key Takeaway

Database statistics are the optimizer's compact model of stored data. Row counts, distinct values, null fractions, ranges, common values, histograms, and column relationships help estimate cardinality and compare execution strategies. They guide plan selection but do not replace indexes, good SQL, sound schema design, or sufficient system capacity.

Manage statistics with evidence. Find the first important estimate error, understand which data characteristic the model missed, and apply the narrowest supported correction. Test representative values, measure maintenance cost, and verify the resulting plan under realistic workload conditions. Accurate statistics are most valuable when they are part of a complete, observable query-performance process.