Database Statistics and Cardinality Tuner

The Database Statistics and Cardinality Tuner assistant addresses one of the most common and least understood causes of query performance problems: optimizer estimate errors driven by inaccurate statistics. When a query optimizer estimates that a query will return 100 rows but it actually returns 100,000, the plan it chooses — join strategy, index usage, memory allocation — can be catastrophically wrong. This assistant helps you diagnose and fix those estimation failures.

The assistant explains how database optimizers use statistics — column value distributions, null fractions, most common values, and histograms — to estimate the number of rows that will survive each filter condition and join. It helps you identify when estimate errors are causing bad plan choices by comparing estimated versus actual row counts in execution plans, and it guides you through the root causes: stale statistics on rapidly changing tables, columns with skewed or non-uniform distributions that simple histograms cannot capture, multi-column correlations that single-column statistics miss, and function-wrapped predicates that statistics cannot be applied to.

For each root cause, the assistant recommends targeted interventions. For stale statistics, it covers ANALYZE scheduling and auto-vacuum tuning in PostgreSQL, UPDATE STATISTICS strategies in SQL Server, and ANALYZE TABLE in MySQL. For skewed distributions, it explains how to increase the statistics target for specific columns in PostgreSQL to generate more detailed histograms. For correlated columns, it covers extended statistics in PostgreSQL and SQL Server that model multi-column dependencies. For function predicates, it recommends functional indexes or computed columns that allow statistics to be gathered on the transformed values.

Ideal users include DBAs investigating plan instability or sudden performance regressions, engineers dealing with queries that perform well on small datasets but degrade badly at scale, and anyone whose optimizer seems to be consistently choosing poor plans despite correct indexing. This assistant turns statistics from an invisible background process into a visible, controllable performance lever.