Query Optimization and Execution Plans Questions

Direct answer. Nested loop is cheapest when the outer side is small and the inner side has a cheap way to be probed (typically an index); hash join is the workhorse for large, unsorted inputs where one side comfortably fits in memory to build a hash table from; sort-merge join wins when both inputs are already sorted (or cheaply sortable) on the join key, since it can then walk both in a single linear pass with no random lookups and no hash table at all.

Structured elaboration. Nested loop's cost is roughly (outer rows) times (cost per inner probe); it degrades badly the moment the outer side is larger than expected, since every extra outer row multiplies the total work. Hash join's cost is roughly (build side rows, to construct the hash table) plus (probe side rows, each a cheap in-memory lookup); its main risk is memory: if the build side is bigger than the memory budget allotted, the hash table spills to disk in partitions, which is far slower than an in-memory hash join but still usually better than a nested loop over the same volumes. Sort-merge needs both inputs sorted; if an index already provides that order, the "sort" part is free and the join itself is a fast linear merge, but if neither input is sorted and both are large, the up-front sort cost can make this the worst of the three options.

Worked example. Joining a 50-million-row fact table to a 200-row lookup table: a nested loop with an index on the fact table's join key (probing the fact table once per lookup row) does roughly 200 cheap index probes, which is far cheaper than building a hash table from 50 million rows. Flip the sizes, joining two roughly-equal, unsorted multi-million-row tables, and a hash join (build from the smaller of the two, probe with the larger) beats a nested loop by orders of magnitude, since nested loop would need tens of millions of probes instead of one hash table build plus one linear probe pass.

Trade-offs and pitfalls. None of the three algorithms is universally best; the right one is a function of the relative sizes of the two inputs, whether either side is already sorted or indexed on the join key, and how much memory is available for a hash table. A senior red flag is seeing a nested loop join over two large, unindexed inputs (that's the shape most likely to be a genuine planner mistake, usually traced back to a cardinality misestimate) versus seeing it over one genuinely small input joined to a large indexed one (that's usually correct and fast).

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. Predicate pushdown means moving a filter as early as possible in a query's execution, ideally all the way down to the storage layer itself, so the engine reads less data from disk in the first place rather than reading everything and filtering afterward in memory.

Structured elaboration. Against columnar formats like Parquet or ORC, files are typically organized into row groups or stripes, each carrying summary statistics (like min/max values) for every column. If a filter can be pushed all the way down to the storage layer, the engine can skip entire row groups whose min/max range can't possibly satisfy the filter, without even reading their column data off disk, which is a much bigger win than filtering rows after they've already been read into memory. The same principle applies through a foreign data wrapper or a similar external-source connector: pushing a filter down to the remote system means less data crosses the network and less work happens locally, versus pulling everything across first and filtering client-side.

Worked example. Consider a wide, JSON-payload-heavy analytics table where a query filters on a narrow, well-indexed timestamp range and only needs a couple of columns. If the filter and the column selection both get pushed down to the storage layer, the engine reads only the row groups whose timestamp range overlaps the filter, and only the specific columns requested, skipping the (often much larger) JSON payload column entirely; if pushdown fails for some reason (the filter is wrapped in a function the storage layer's statistics can't reason about, for instance), the engine instead reads every row group's relevant columns into memory and applies the filter there, doing far more I/O for the identical logical result.

To verify pushdown is actually happening rather than assumed, check your engine's plan output for an explicit indicator (many engines report something like "filter pushed down" or a bytes-read figure noticeably smaller than the table's full size on disk) rather than trusting that a filter which LOOKS pushdown-friendly actually was.

Trade-offs and pitfalls. Pushdown eligibility is sensitive to exactly how a predicate is written, the same logical filter expressed with a function wrapped around the column, or compared against a mismatched type, can silently fail to push down even though a semantically-equivalent bare-column comparison would have; this is the same underlying sensitivity that defeats sargability for index usage, applied one layer further down the stack.

Direct answer. IN and EXISTS both test membership but differ in how NULLs interact with them and in how each maps onto a join in the optimizer's mind; JOIN differs from both in that it's row-producing rather than boolean, so it only behaves like a membership test if the inner side is guaranteed unique per outer row.

Structured elaboration. WHERE x IN (subquery) is true if x matches any row the subquery returns; critically, if the subquery's result set contains even one NULL and x doesn't match any non-NULL value, the whole expression evaluates to UNKNOWN rather than false, which under NOT IN specifically can silently make the entire outer predicate evaluate to nothing at all (a classic, expensive-to-debug correctness trap, not just a performance one). WHERE EXISTS (correlated subquery) instead asks only "does at least one matching row exist," is unaffected by NULLs in the same way, and lets the engine short-circuit as soon as one match is found rather than materializing a full list to compare against. A plain JOIN used for the same membership-testing purpose can silently change the row count of the outer query if the inner side isn't unique per join key, duplicating outer rows once per match, which neither IN nor EXISTS does since they only ever return true or false.

The optimizer is often free to transform one of these into another internally, when they happen to be logically equivalent for that specific query, so the choice between them is not always a performance decision. Where it genuinely matters, EXISTS's short-circuit behavior tends to help most when only a small fraction of outer rows actually have any match, and its immunity to the NULL trap makes it the generally safer default whenever the inner column can contain NULLs.

Worked example. For customers where you want those with at least one high-value order, IN (SELECT customer_id FROM orders WHERE amount > 1000) and EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.amount > 1000) return identical results as long as customer_id in orders never contains NULL; if it can be NULL and you instead wrote NOT IN (...) to find customers WITHOUT a high-value order, that query can silently return zero rows the moment even one NULL customer_id exists in orders, while the equivalent NOT EXISTS form is unaffected.

Trade-offs and pitfalls. Prefer EXISTS/NOT EXISTS over IN/NOT IN whenever the inner column isn't guaranteed NOT NULL, purely for correctness, independent of any performance difference; and never reach for a plain JOIN as a membership test unless you've confirmed the inner side is unique per join key, or wrap it in a DISTINCT to restore the semantics you actually wanted.

Complexity

All three can, in principle, execute as a semi-join internally when the engine recognizes the pattern, so there is often no fundamental asymptotic difference; the practical differences come from NULL handling and from whether the engine actually recognizes the equivalence for a given query shape.

Edge cases

NOT IN against a column that can contain NULL is the single highest-risk pattern here and deserves an explicit check (or an automatic ban) in code review, since it fails silently rather than with an error.

Direct answer. Every index has to be updated on every INSERT, UPDATE (of an indexed column), and DELETE against that table, so more indexes mean more work per write; decide whether a proposed index is worth that cost by weighing the read-side latency or resource savings it would provide against the table's actual write rate and the query mix's read-to-write ratio.

Structured elaboration. When a row is inserted, every index on that table needs a new entry added, not just the table's own storage; when a row is updated in a way that changes an indexed column's value, the old index entry has to be removed (or marked for cleanup) and a new one added; when a row is deleted, every index entry pointing at it needs equivalent cleanup. On a table with a high write rate, this compounds: five indexes doesn't just mean "five times the storage," it means every single write pays five times the index-maintenance cost, which shows up as both slower individual write latency and more total background maintenance work for the storage engine to clean up over time.

To decide whether a proposed index is worth it: estimate how much read latency or resource cost it would actually save (using the current, unindexed plan's cost as the baseline), estimate the added per-write cost using the table's actual write rate (not the read rate), and weigh those against each other in terms of what the table's ACTUAL query mix looks like, not a generic assumption. A table with a 100:1 read-to-write ratio can usually absorb several more indexes without much visible cost; a table receiving thousands of writes per second, where reads are comparatively rare, needs a much higher bar before adding another index is worth it.

Worked example. Adding an index that takes a reporting query from multi-second and painful down to near-instant, on a table that's written to only a few times a minute, is an easy call: the write-side cost is negligible relative to the read-side win. The identical index added to a high-throughput event-ingestion table receiving thousands of writes per second, where that specific report only runs a handful of times a day, is a much harder call, since the aggregate write-side cost (paid on every single one of those writes, all day, every day) could dwarf the occasional report's savings.

Trade-offs and pitfalls. It's easy to evaluate an index purely on its read-side benefit and never quantify the write-side cost at all, especially since the write-side cost is diffuse (a small increment on every write) rather than a single visible number the way a slow query's latency is; make the write-side estimate explicit, even roughly, rather than skipping it.