Study guides · Intermediate

Query estimates: find the first row-count divergence

Connect statistics and selectivity to row estimates, then compare plans and actual execution without confusing per-loop counts or correlated predicates.

Ask Logeia about this articleTry the three questionsReview your answers

Updated

On this page
  1. Row estimates influence plan selection
  2. Calculate a simple filter estimate
  3. Inspect dependence between predicates
  4. Read actual rows with loop counts
  5. Locate the first divergence
  6. Test more than one parameter distribution
  7. Ask Logeia about this articleCheck your understanding

Row estimates influence plan selection

A query optimizer compares candidate access paths and join strategies using estimated costs. Expected row counts are central inputs to those calculations. A cheap lookup repeated ten times can be attractive, while the same lookup repeated one hundred thousand times can dominate execution. Do not classify nested loops as inherently bad or indexes as universally best from plan names alone. First identify the assumed input size and conditions. Estimation errors and inaccuracies in the cost model are different issues. Correcting a row count does not automatically make every cost parameter accurately represent the actual hardware, cache state, or concurrent workload.

Calculate a simple filter estimate

One million rows filtered by an estimated two-percent selectivity gives twenty thousand output rows. Assuming an even split merely because a status column has two values can be wrong when the data is skewed. PostgreSQL uses statistics such as common values and histograms to estimate distributions. These are not an exact distribution maintained after every individual update; sampling and update timing matter. Bulk ingestion can change the data substantially before statistics reflect it. Inspect the data range and statistics freshness before deciding which mechanism explains an estimate. A stale statistic and an unsupported relationship between columns require different remedies.

Inspect dependence between predicates

Predicates on region and postal code can be strongly dependent. If each individually selects one percent, their conjunction need not select 0.01 percent. When one largely determines the other, multiplication can underestimate the result. Extended statistics can help with selected multicolumn relationships, but they do not universally correct every expression or join estimate. Check which statistic type is applicable to the actual predicates. A distributional dependency collected for planning is also different from an enforced business constraint. Creating statistics does not enforce referential integrity, and a valid schema constraint does not imply that every planner estimate will be exact.

Read actual rows with loop counts

EXPLAIN ANALYZE executes the statement, so avoid casually applying it to updates or expensive production queries. In a suitable environment, compare estimated rows with actual rows while checking loop counts. For repeatedly executed nodes, displayed actual rows are per-execution averages. Five rows over one hundred loops represent roughly five hundred rows in total, with display rounding to consider. Parent timings include child work, so summing every node’s elapsed time is not a valid total-runtime calculation. Align units before connecting a row-count error to repeated work or elapsed time. The plan is evidence to interpret, not a table whose numbers can all be added indiscriminately.

Locate the first divergence

A large mismatch at the top of a plan may originate earlier. Follow scans and filters upward to find the first meaningful divergence. A filter estimated at twenty rows but producing five thousand supplies underestimated input to later joins. Investigate that predicate’s skew, statistics, expression, and parameters before forcing a different upper-level join strategy. A forced plan that helps one parameter can hurt another. Change one hypothesis at a time, preserve the same query and input for comparison, and verify equal results. Record which discrepancy the change explains rather than reporting only that one execution became faster.

Test more than one parameter distribution

A few customers may have vastly more orders than others, so one SQL statement can face very different row counts for different identifiers. Include common and extreme cases rather than optimizing only a tiny result. Separate cold and warm execution and consider concurrency. Increasing statistics targets, running ANALYZE, creating extended statistics, or changing an index or query each has creation, maintenance, or planning costs. Select a remedy that addresses the tested hypothesis. A favorable result for one parameter is useful evidence but does not establish a universally optimal plan for a skewed workload or every future data distribution.

Reading and practice are recorded separately in this browser, for each language.

Learn this in order

Understand the systems behind the web · STEP 12

Choose a learning path