Analyzing Cost-Based Optimizer Cardinality Estimates
In this lesson, you will learn how to detect the silent performance killers in your query execution plans by evaluating the accuracy of the Cost-Based Optimizer’s (CBO) row count predictions.
The CBO is the "brain" of your database engine, responsible for choosing the most efficient join order and access path. To do this, it relies on cardinality estimates—the predicted number of rows returned by each operator. When these estimates are wildly inaccurate, the optimizer makes poor decisions, such as choosing a nested loop join when a hash join would be orders of magnitude faster, leading to bloated cloud compute bills and sluggish pipelines.
Identifying the Discrepancy
A significant discrepancy exists when the Estimated Rows (what the optimizer thought would happen) differs drastically from the Actual Rows (what happened during execution). You can usually find these in an EXPLAIN ANALYZE output.
Consider a scenario where you are joining a massive orders table with a filtered users table:
EXPLAIN ANALYZE
SELECT o.id, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.status = 'ACTIVE';
If the execution plan shows:
- Estimated Rows: 10
- Actual Rows: 1,000,000
The CBO likely assumed the status filter was highly selective, so it opted for a nested loop join. Because the actual result set was huge, your query engine is now performing a million random lookups, hammering CPU and I/O.
Why Estimates Fail
Cardinality estimates typically drift due to stale statistics. If your ETL pipelines have recently ingested millions of rows but the database hasn’t performed an ANALYZE or RUNSTATS operation, the optimizer is making decisions based on obsolete data. Additionally, correlated columns (e.g., filtering on city and zip_code simultaneously) often confuse optimizers that assume independence, leading to "under-counting" that forces inefficient join strategies.
When you spot these gaps, your remediation path is clear: update table statistics, verify that your data distribution hasn't become skewed, or use query hints if the optimizer is persistently unable to model the data complexity.
Key Takeaway: Significant discrepancies between estimated and actual cardinality cause the CBO to select sub-optimal join algorithms, which manifests as excessive resource consumption and increased query latency.