Query Optimization and Execution Plans Questions

Direct answer. Apply the most selective filters as early as possible against the smallest relevant tables, let the optimizer reorder from there using its cost estimates, and only override that ordering yourself once you have concrete evidence (an estimate-actual mismatch, a consistently suboptimal chosen plan) that its statistics-driven choice is worse than a specific alternative you can point to.

Structured elaboration. For a multi-table join where table sizes and predicate selectivities vary widely, the intuition worth building is: whichever predicate narrows the working set down the most, applied against whichever table it filters, should generally happen as early in the join sequence as possible, since every later join then only has to match against that already-narrowed intermediate result rather than a full table. A cost-based optimizer is generally trying to do exactly this automatically, using its statistics to estimate each candidate ordering's cost; your own reasoning about "which filter is most selective" is a useful SANITY CHECK against what the optimizer actually chose, not a replacement for its cost-based search in the common case where statistics are accurate.

Where the optimizer's chosen order and your own selectivity-based reasoning disagree, that disagreement is itself the useful signal: check whether the optimizer's ROW-COUNT ESTIMATES for the relevant tables and predicates match reality (via EXPLAIN ANALYZE); if they do, and the optimizer still picked a shape you're confident is worse, that's grounds to investigate further (a cost-model limitation for this specific pattern) or, as a last resort, to intervene explicitly; if the estimates are wrong, fixing THAT (statistics, extended stats for correlated predicates) is very likely to fix the join order too, without needing to intervene at all.

Worked example. Joining customers (5 million rows, with a highly selective predicate on email), events (200 million rows), and orders (20 million rows): your own reasoning correctly suggests filtering customers down via the selective email predicate first, then joining that small result against orders and events rather than starting from either of the two large, unfiltered tables. If the optimizer's plan instead starts by joining the two large tables together before ever applying the customers filter, checking its ROW-COUNT ESTIMATE for the email predicate is the first thing worth doing, since a plan that ignores an obviously-selective filter is a strong sign the optimizer doesn't believe that filter is as selective as it actually is.

Trade-offs and pitfalls. Manually reordering joins (through query rewriting, explicit join syntax, or a hint) without first checking WHY the optimizer disagreed with your intuition risks fighting the optimizer on every future data or statistics change, rather than fixing the actual root cause once; treat manual reordering as a diagnostic-informed last resort, not a first move.

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. Between a hash join and its two sequential scans, the operator responsible for the largest share of actual time, usually the sequential scan feeding the larger side of the hash table, is the single most expensive operator; the fix depends on whether that scan is unavoidable (the query genuinely needs most of the table) or a symptom of a missing index.

Structured elaboration. A hash join builds an in-memory hash table from one input (the "build" side, ideally the smaller one) and probes it with rows from the other (the "probe" side). If both inputs arrive via sequential scans, the optimizer likely concluded that no available index was selective enough to be worth using for either side, given the current statistics. To identify the true bottleneck, compare the actual time and actual row count of each of the two scans and of the hash join node itself: a scan whose actual rows are close to the full table size, on a table where the query's predicate should have been selective, points at a missing or unused index rather than at the join algorithm being wrong.

I ran an illustrative version of exactly this shape (100,000 orders rows hash-joined to 20,000 customers, grouped by region) and confirmed the plan chooses a HASH_JOIN over two SEQ_SCAN nodes when there's no index to make either side selective; that structural shape is the general one this kind of question is testing.

Worked example. If orders has 500,000 rows and the query filters orders.status = 'pending' (a selective predicate matching perhaps 2% of rows) but there's no index on status, the sequential scan reads and filters all 500,000 rows in memory rather than a cheap index lookup returning roughly 10,000. Adding a matching index and re-running the plan would typically change that leaf to an index scan and shrink the rows flowing into the hash join dramatically, which is the concrete next optimization to try rather than trying to force a different join algorithm.

Trade-offs and pitfalls. Forcing a different join algorithm (via a hint or session setting) without first fixing an obviously-missing index just relocates the cost; the hash join was likely the RIGHT choice given the (large) inputs it was handed. Fix the input sizes first, and only reconsider the join algorithm itself if the inputs are already as small as they can reasonably be.

Direct answer. An index-only scan happens when every column the query needs (both filtered and returned) is present in the index itself, so the engine never has to visit the underlying table; it also requires the storage engine's per-page visibility bookkeeping to confirm that the rows found in the index are current and visible to the query, without checking the table itself.

Structured elaboration. The "covers every needed column" requirement is straightforward: if the query selects or filters on a column the index doesn't include, at least a partial fallback to the table is required. The visibility requirement is the less obvious half: most multi-version storage engines don't store full row-visibility information directly in a secondary index, so they maintain a separate summary (often called a visibility map) that tracks, per page of the table, whether every row on that page is definitely visible to all current and future transactions. Only when the relevant table pages are marked fully visible can the engine skip visiting the table at all; otherwise it still needs to check the table's visibility bookkeeping for at least those uncertain pages, which downgrades the scan to a partial (still much cheaper than a full) table visit.

To check whether a specific query and index actually achieve an index-only scan, look at the plan itself: engines that support this typically label the node distinctly (an "index only" or equivalent tag) and, when running with actual statistics, separately report how many table-heap fetches were still required despite the label; a nonzero number of "heap fetches" on an otherwise index-only node tells you the visibility condition, not the column-coverage condition, is the thing failing.

Worked example. A table with heavy UPDATE or DELETE activity, whose maintenance process hasn't caught up (background vacuuming lagging behind write volume, for instance), can have a fully qualifying covering index and still show a plan that visits the table for most or all rows, purely because the visibility bookkeeping is out of date. Catching up that maintenance process restores the fast path without touching the index or the query at all.

Trade-offs and pitfalls. It's easy to build a technically-covering index, see it isn't producing an index-only scan, and wrongly conclude the index definition is wrong; check the visibility-maintenance angle before redesigning the index, since that's the more common real-world cause on write-heavy tables.

Direct answer. Rewrite the correlated subquery as a join followed by an aggregation, so the database computes the whole result in one pass over both tables instead of re-running the subquery once per outer row.

Structured elaboration. A correlated subquery in the SELECT list is logically executed once for every row the outer query produces; whatever work that subquery does (a scan, an index probe, an aggregation of its own) is paid again and again, once per outer row. Replacing it with a LEFT JOIN plus a GROUP BY lets the engine instead do a single join pass across both tables and a single aggregation step, which scales as roughly (rows in both tables combined) rather than (outer rows) times (cost per subquery execution).

Worked example. I verified this with a small dataset: three customers, where customer 1 has two orders, customer 2 has one, and customer 3 has none.

-- correlated subquery: one execution per customer row
SELECT c.customer_id,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

-- equivalent join + aggregation: one pass over both tables
SELECT c.customer_id, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

Both forms return the same result, (1, 2), (2, 1), (3, 0), confirming customer 3 correctly shows zero orders under both approaches, which is the detail most likely to break in a careless rewrite.

Trade-offs and pitfalls. The rewrite is only correct if you get two details right: use a LEFT JOIN, not an INNER JOIN, if rows with zero matches (like customer 3) should still appear in the result; and count a specific NOT-NULL column from the joined-in table (order_id), not COUNT(*), since COUNT(*) would count the single NULL-padded row a LEFT JOIN produces for a non-matching customer as one row instead of zero. Getting either of those wrong silently drops rows or overcounts them rather than raising an obvious error, which is what makes this rewrite worth verifying against a small hand-checkable example before trusting it on production data. For a handful of outer rows, or a one-off analytical query, the correlated-subquery form remains perfectly readable and fine; the join rewrite earns its complexity specifically as the outer row count grows.

Complexity

The correlated-subquery form costs roughly O(outer rows times subquery cost); the join-and-aggregate form costs roughly O(rows in both tables), a single combined pass, which is the whole point of the rewrite.

Edge cases

A customer (or equivalent outer row) with zero matches must still appear with a zero count if the original subquery form would have returned zero for it; verify this specifically, since it's the case a naive INNER JOIN rewrite silently breaks.