Query Optimization and Execution Plans Questions

Direct answer. The most common reasons a planner falls back to a nested loop instead of the hash join you expected are a build-side row-count estimate that's too low to justify the hash table's setup cost, a memory budget too small to hold a reasonably-sized hash table, or an available index on the inner side that made a nested loop look cheap on paper even though the real cardinality doesn't support that.

Structured elaboration. First, an underestimated build side: if the optimizer believes one side will only produce a handful of rows (because of a cardinality misestimate), a nested loop probing an index for each of those "few" rows looks cheaper than the fixed overhead of building a hash table, even when the true row count is huge. Second, a memory ceiling: if the per-query memory allowance is small relative to the smaller input, the optimizer may (correctly, given its own cost model) judge that a hash join's disk-spilling behavior would be more expensive than a nested loop, even though in practice the nested loop turns out worse. Third, an index that superficially supports a cheap per-row probe: if the inner table has an index on the join column, the planner may lean toward using it via a nested loop rather than paying for a hash build, purely because its cost model rates indexed lookups cheaply per-row without yet knowing how many outer rows will actually drive those lookups.

To fix it: update statistics on the tables involved (addresses cause one, the most common); raise the per-query memory setting if the environment allows it (addresses cause two); or, as a last resort while you investigate further, force the alternate algorithm at the session level to confirm the hash join actually would be faster, which gives you evidence rather than a guess before you decide on a permanent fix.

Worked example. A training-data extraction query that joins to a low-cardinality dictionary table might correctly use a nested loop, since the dictionary side really is tiny; the same shaped query joining two multi-million-row tables choosing a nested loop is much more likely to be case one or two above, not a correct decision.

Trade-offs and pitfalls. Forcing a hash join via a session setting is a good diagnostic step but should not become the permanent fix; if the real fix is stale statistics or a genuine memory constraint, forcing the algorithm just masks the symptom until the next data shift makes a different plan wrong again.

Direct answer. A dashboard that had quietly grown from a few hundred thousand rows to several million was taking eight to twelve seconds to load; I traced it to a sequential scan feeding a nested loop join whose row-count estimate was badly stale, fixed it with a statistics refresh plus a targeted composite index, and got it back under a second.

Structured elaboration. Situation: an internal reporting dashboard, previously fast, had degraded gradually enough that no single deploy looked like the cause, and initial suspicion fell on the application layer rather than the database. Task: find the actual bottleneck and fix it without a major schema change, since the dashboard needed to stay available. Action: I ran EXPLAIN ANALYZE against the slow query directly (rather than trusting application-level timing alone) and found the estimated row count for the main filter was off by roughly two orders of magnitude from the actual, which had pushed the optimizer toward a nested loop join that made sense for the old, smaller estimate but was disastrous for the real, current volume; I confirmed a stale-statistics diagnosis by checking when statistics had last been refreshed on the affected table (a while before the data had grown), refreshed them, and added a composite index that matched the query's actual filter-plus-sort pattern, which the old, smaller table hadn't needed.

Result: dashboard load time dropped from the eight-to-twelve-second range to under one second, confirmed by re-running EXPLAIN ANALYZE and seeing the plan switch to an index scan with estimated and actual row counts now closely matched, plus a follow-up check a week later confirming the improvement held under real traffic rather than being a one-off measurement.

Trade-offs and pitfalls. The fix itself was straightforward once correctly diagnosed; the harder part was resisting the urge to guess at an application-layer cause first, since the symptom (a "slow page") didn't obviously point at the database until I actually looked at a real execution plan rather than just a wall-clock timing number.

Direct answer. Walk through your triage process for narrowing down what changed: what evidence you would gather first, the handful of underlying causes that pattern is usually explained by, and how you would confirm which one actually happened rather than guessing.

Structured elaboration. Start by gathering evidence that distinguishes "the plan changed" from "the plan is the same but the work it does grew": compare the current plan to a known-good baseline if one is available (many engines let you capture and diff plans, or at minimum you can re-run EXPLAIN and eyeball the operator shapes), and check whether the DATA changed (row count growth, a new skewed value, a bulk load) independent of any plan change. From there, the most common root causes for this "regressed with no application change" pattern are, in roughly descending order of how often they turn out to be the actual cause: statistics that went stale after a data change (a bulk load or a big delete that wasn't followed by a refresh); a cached plan compiled for an atypical parameter value now being reused for a much more typical, much less selective one (a parameter-sniffing regression); the DATA itself simply growing or shifting distribution past a threshold that used to favor one strategy and now favors another; and resource contention from something unrelated to this specific query (concurrent load, a maintenance job, disk pressure) that's slowing everything down and only looks query-specific because that's the query someone happened to notice first.

To confirm which one actually happened rather than just picking the most common cause and hoping: check statistics freshness and the estimated-vs-actual row counts at the plan's key nodes first, since that single check distinguishes the stale-statistics and parameter-sniffing causes (both would show a mismatch) from the pure-resource-contention cause (which typically wouldn't); if estimates and actuals both look reasonable and current, pivot to checking system-level resource metrics (CPU, I/O, lock waits) for the affected time window instead.

Worked example. A nightly report that quietly regressed after last week's data load added a disproportionate number of rows in one previously-small category is a textbook case: the statistics captured before the load still describe the smaller, more balanced distribution, so the optimizer's row-count estimate for that category is now badly wrong, and a plan built around that bad estimate (likely favoring an index-heavy strategy suited to the old, smaller volume) is now handling a much larger actual volume than it was ever appropriate for.

Trade-offs and pitfalls. It's tempting to jump straight to "just refresh statistics and see if that fixes it" without confirming that's actually the cause; that's a reasonable first cheap experiment to try, but treat a fix that "seems to help" with some skepticism until you've actually looked at the estimated-vs-actual gap directly, since a coincidental unrelated change (contention easing off on its own) can make an unrelated fix look like it worked.

Direct answer. Confirm it's a cardinality problem, not something else, by checking whether the estimate-to-actual gap is localized to one filter step and whether that step's predicate involves correlated columns, a recent bulk load, or a parameter value the optimizer hasn't seen before; then fix it from the cheapest, least invasive remediation upward.

Structured elaboration. A nested loop join is a reasonable choice when the outer side is expected to be small, because the cost of the inner probe, repeated once per outer row, stays manageable. If the actual outer row count is orders of magnitude larger than estimated, the same plan shape becomes catastrophic, because the cheap-per-iteration inner probe now runs far more times than budgeted. To confirm this diagnosis (rather than, say, a genuinely slow inner probe or lock contention), look specifically at the filter or join step whose actual rows diverge most from its own estimate, and ask whether its statistics are current, whether the predicate involves multiple correlated columns your optimizer doesn't model jointly by default, or whether this is a parameterized query whose plan was compiled for a very different, more selective parameter value.

Remediation, cheapest first: run an updated ANALYZE (or the equivalent) on the affected table and re-check the plan, since stale statistics are the most common and cheapest-to-fix cause; if the columns involved are correlated, consider extended or multi-column statistics rather than assuming the single-column stats will ever be enough; if it's plan-reuse across very different parameter values, consider recompiling for the current parameter or disabling plan caching for that specific query. Only reach for a query rewrite or schema change once the above are ruled out, since those are far more invasive for what is often, in practice, a stale-statistics problem.

Worked example. In a staging environment you could test the hypothesis directly by temporarily disabling the nested loop strategy for that session (most databases expose a setting for this) and re-running the same query: if the alternate plan the optimizer is forced into completes fast, that's strong confirmation the original nested loop, not the underlying data volume, was the actual problem, and you can move forward confident that fixing the estimate (rather than something else entirely) is the right lever.

Trade-offs and pitfalls. Disabling a join strategy at the session or query level is a fine DIAGNOSTIC tool but a risky permanent fix, since it blinds the optimizer for every other query on that connection too; treat it as something you turn off again the moment you've confirmed your hypothesis.

Direct answer. Selectivity is the fraction of a table's rows a predicate matches; cardinality is the actual number of rows that come out of an operation. A highly selective predicate (one matching few rows) is exactly the situation where an index earns its cost, and predicate selectivity is also the main input the optimizer uses to decide join order, since it wants to apply the most narrowing filters as early as possible.

Structured elaboration. If a predicate's selectivity is 1% (it matches roughly 1 in 100 rows), an index lookup that jumps straight to those rows beats scanning the whole table, since the fixed cost of the index lookup is repaid many times over by the rows it lets you skip; a predicate matching 50% of rows offers much less benefit from an index, since you'd be visiting half the table's rows either way, just via a more expensive access pattern. For join order, the optimizer generally wants to apply the predicate (or combination of predicates) that will shrink the working set the most, as early in the join sequence as possible, since every table joined afterward then only has to be matched against a smaller intermediate result rather than the full one.

To estimate a predicate's selectivity from existing statistics without running the full query, you'd typically look at the number of distinct values for that column (assuming a roughly uniform distribution, selectivity for an equality predicate is close to 1 divided by the distinct value count) or, where available, a stored histogram that captures the actual distribution more precisely for skewed columns.

Worked example. For a users table with a country column where 90% of rows are 'US' and the remaining 10% are spread across 40 other countries, filtering country = 'US' has low selectivity (matches ~90% of rows, an index doesn't help much, a scan is likely the right choice), while filtering country = 'Luxembourg' has high selectivity (matches a tiny fraction, an index on country earns its cost here even though it's the SAME column and the SAME index).

Trade-offs and pitfalls. Selectivity isn't a fixed property of a column, it's a property of a specific predicate VALUE against that column's distribution; assuming one index is uniformly "good" or "useless" for a column without accounting for which specific values are queried most is a common estimation mistake, and it's exactly why skewed columns need better-than-average statistics (histograms or multi-value frequency counts) to plan well.