Trace row-count assumptions before judging index use
PostgreSQL estimates the number of rows passing predicates and uses those estimates when choosing scans, join order and join methods. If a small-input assumption is wrong, repeated probes or sorting can become far more expensive than expected. An apparently slow plan should therefore not immediately be reduced to a claim that an index is missing. Read the expected population at each node. A large error near the root may simply amplify a wrong assumption introduced lower down. Find where divergence first becomes substantial and ask which predicate or relationship supplies that assumption. An estimate error is not automatically the main latency cause: lock waits and storage constraints can coexist with it. Combine row-count evidence with execution and waiting observations before selecting a remedy.
Put estimates and observations on comparable terms
Rows in an ordinary EXPLAIN are estimates, while actual rows in EXPLAIN ANALYZE describe execution. For a repeatedly executed node, read the row figure together with loops and distinguish per-loop values from total work. Ten returned rows across one thousand loops imply roughly ten thousand emitted rows. Node timings also have inclusive relationships, so adding every parent and child duration can double-count work. Estimated cost is a model quantity rather than automatically milliseconds. EXPLAIN ANALYZE really executes the statement, including possible modifications. Preserve the SQL, parameters, data population, version and configuration in a suitable test environment, and compare the estimate with the observation for that same operation.
Construct a correlated-predicate example
Consider a hypothetical table of ten thousand orders. Country X matches one thousand and city Y matches one thousand, with every Y row belonging to X. Treating the predicates as independent suggests multiplying two selectivities of 0.1 and estimating one hundred matching rows. In the invented data, the intersection actually contains one thousand. Refreshing separate column distributions does not remove the dependency if the calculation still assumes independence. This is a worked reasoning example, not a production measurement. Establish the predicate’s real meaning, including casts, functions, nulls and skewed values. Similar column names do not prove a particular relationship. Increasing statistics without identifying the missing information can spend collection effort without addressing the actual estimate.
Choose statistics within their supported scope
ANALYZE is a candidate after bulk changes or a distribution shift. For related columns, choose an appropriate extended-statistics mechanism such as dependencies, common value combinations or distinct-combination counts. Each mechanism has a supported scope: it does not automatically repair arbitrary predicates or every join estimate. Creating a statistics object alone is not the same as collecting its data, so verify the required analyze step and the installed version’s behavior. Higher targets also cost collection time and storage. Explain what information the problematic decision needs, then check whether the first divergence improves and whether that improvement affects the intended choice. Changing numerous broad settings together obscures which hypothesis was actually tested.
Separate plan changes from performance gains
A better estimate and a different join method do not guarantee a faster response. The suitable method depends on population and cache conditions; nested loops are not inherently defective. Hash or sort spills, I/O, transfer volume and concurrent activity can dominate the total. Adding an index increases retrieval options but also creates write-maintenance costs. Reducing estimate error is a means to an application objective rather than an objective sufficient by itself. Compare plan structure, actual populations, resource use and successful throughput under the same work. A more attractive diagram or a smaller estimation ratio is useful evidence, but not proof that the user-facing regression has been repaired or that other operations remain unaffected.
Preserve reproductions and alternative inputs
Retest common parameter values as well as the reported bad case, including empty results, unusually large customers and boundary periods. Forcing one plan for a single value can produce severe regressions for another population or a future distribution. When relevant, distinguish prepared-statement generic plans from value-specific planning. Preserve the original divergence, supporting distribution evidence, chosen statistics or SQL change, and final application metric. A failed intervention is informative if the record distinguishes an unsupported estimation mechanism from a different dominating resource. The task is not choosing whether to trust the optimizer in the abstract. It is comparing the model’s assumptions with data and applying one explainable, reproducible change at a time.
Separate retrieval from client-side processing when the query returns many rows. A fast server plan does not establish fast rendering, decoding or network transfer for the user.