Query Optimization and Execution Plans Questions

Direct answer. A merge join needs both inputs to already be sorted on the join key (either because an index provides that order for free, or because the optimizer decided an explicit sort was worth paying for), and it wins over a hash join specifically when that sortedness is cheap to obtain and the two inputs are both large, since it then needs no in-memory hash table at all, just a single linear walk through both sorted streams.

Structured elaboration. Given two inputs sorted on the join key, a merge join advances through both in lockstep, comparing the current key from each side and advancing whichever is behind, producing matches as it goes; this is a single linear pass with essentially no extra memory needed beyond tracking the current position in each input, which makes it very cheap once the sortedness is already available. A hash join instead builds an in-memory hash table from one side (a real memory cost, and a potential spill-to-disk cost if that side is large) and probes it with the other; it doesn't need either input sorted, which is exactly why it's the more common default for two large, unsorted inputs.

Merge join suffers when the required sortedness ISN'T already available and has to be produced with an explicit sort step, since sorting a large input from scratch can be as expensive as (or more expensive than) simply building a hash table would have been, at which point the merge join's own advantage evaporates and a hash join becomes the better choice. To prepare data to make a merge join a live, attractive option, ensure both sides have an index on the join key (so the sort comes "free" from index order) or that the join key is already the table's natural clustering order, rather than relying on an explicit, expensive sort step to manufacture that order on the fly.

Worked example. Joining two large fact tables that are BOTH naturally maintained in sorted order on the join key (say, both clustered or indexed on the same surrogate key by construction) is close to the ideal case for a merge join: the sortedness is free, and the join itself becomes a cheap linear pass with no hash table memory pressure at all, which can beat an equivalent hash join, especially under tight memory constraints where a large hash table would otherwise risk spilling.

Trade-offs and pitfalls. Don't assume a merge join is "the sophisticated choice" and therefore always preferable; it only wins when sortedness is genuinely cheap, and forcing one via a hint on two unsorted large inputs, paying for an explicit sort neither input naturally had, is very often worse than just letting the optimizer's default hash join choice stand.

Direct answer. Query system catalogs and usage-tracking views for both index usage counts (to spot ones that are never or rarely scanned) and index definitions (to spot ones that are subsets or exact duplicates of other indexes); before actually dropping anything, confirm across a representative time window and check for uses outside plain SELECT queries, like enforcing a uniqueness constraint, that usage stats alone won't show.

Structured elaboration. Most relational databases expose a system view or catalog that reports, per index, how many times it's been scanned since statistics were last reset; an index with a usage count of zero (or near-zero) over a long enough observation window, on a table that otherwise sees real traffic, is a strong candidate for being unused. For redundancy, compare index DEFINITIONS directly: an index on (user_id) is functionally redundant if another index on (user_id, order_date) already exists, since any query that could use the narrower index can use the leading portion of the wider one just as well; two indexes with the exact same column set in the same order are pure duplicates.

The evidence bar before actually dropping an index should be higher than "usage count looks low right now." Observe over a window long enough to cover your real traffic patterns (including infrequent batch jobs or month-end reports that only run periodically), and separately verify the index isn't silently required for something usage stats won't show, most commonly enforcing a UNIQUE or primary-key constraint, or being the specific index a foreign-key relationship depends on for efficient cascade operations.

-- illustrative shape (exact catalog names vary by engine):
-- 1) find indexes with (near) zero scans over the observation window
SELECT index_name, scans FROM pg_stat_user_indexes WHERE scans < 10;
-- 2) find indexes whose column list is a strict subset of another index's
--    on the same table, in the same leading order

Trade-offs and pitfalls. Dropping an index that turns out to matter (even rarely) can cause a sudden, hard-to-diagnose regression the next time that infrequent query or batch job runs; a safer rollout pattern is to disable (rather than immediately drop) a candidate index where the engine supports that, monitor for any regression over a full business cycle, and only drop it permanently once you're confident nothing depends on it.

Complexity

This is a metadata-driven audit, not a per-row scan of the table itself, so it's cheap to run repeatedly regardless of table size; the expensive part is the calendar time needed to observe a representative traffic window, not the query cost.

Edge cases

An index that backs a UNIQUE or FOREIGN KEY constraint will often show up as "unused" in scan-count views even though it's structurally required; always cross-check the constraint catalog before treating a low-scan index as a safe drop.

Direct answer. Each physical operator represents a different strategy for reading or combining rows, and the optimizer's whole job is to pick a combination of these that minimizes total estimated cost for your specific query and data. Scans get rows out of storage; joins, sorts, and aggregates combine or reorganize the rows those scans produced.

Structured elaboration.

• A sequential scan reads every row of a table (or heap) in physical order. It's chosen when a large fraction of the table is needed, or when there's no useful index; its cost scales with table size regardless of how selective the filter is.
• An index scan walks an index structure to find matching rows, then fetches the full row from the table for each match. It's chosen when the predicate is selective enough that visiting the index plus a handful of table rows beats reading the whole table; the trade-off is that each match costs a separate (often random) I/O against the table.
• An index-only scan answers the query entirely from the index, with no visit to the table, when every column the query needs is present in the index and the storage engine's visibility bookkeeping allows it. This is the cheapest access method when it applies.
• Nested loop, hash, and merge join each combine two row sets differently: nested loop probes the inner side once per outer row (cheap for a small outer side with a cheap way to probe the inner side); hash join builds an in-memory hash table from one side and probes it with the other (good for large, unsorted inputs); merge join walks two already-sorted inputs in lockstep (good when both sides are cheaply available in sorted order).
• Sort materializes rows in a required order, which costs memory or disk depending on volume; aggregate collapses rows into groups, either via a hash table (unsorted input) or by exploiting already-sorted input.

Worked example. For SELECT customer_id, SUM(amount) FROM orders WHERE created_at >= '2025-01-01' GROUP BY customer_id, a reasonable plan is: an index scan on created_at to find recent rows cheaply (assuming that predicate is selective), feeding a hash aggregate that groups by customer_id using an in-memory hash table, since there's no reason to expect the rows already arrive sorted by customer.

Trade-offs and pitfalls. None of these operators is unconditionally "the good one" or "the bad one." A sequential scan is the CORRECT choice, not a mistake, when a query needs most of a table's rows, because the per-row overhead of index lookups would cost more in aggregate. The signal worth watching for in a plan is a mismatch between the operator and the actual selectivity or volume involved, for example a nested loop join running many more iterations than its own estimate expected.

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. Parameterized queries and prepared statements keep user-supplied values out of the SQL text itself, so they're passed and bound as data rather than interpreted as part of the query's structure, which is what prevents SQL injection; the same separation also lets the database compile the query's STRUCTURE once and reuse that compiled plan across many executions with different parameter values, avoiding repeated compilation cost.

Structured elaboration. A query built by directly concatenating user input into SQL text asks the database to parse that entire string, including the injected input, as part of the query's grammar, which is exactly the vulnerability an attacker exploits by supplying input that changes the query's actual structure. A parameterized query instead sends the query's fixed structure (with placeholders) separately from the parameter values; the database parses and plans only the fixed structure, and the values are bound in afterward as pure data, never re-interpreted as SQL syntax, which closes off that entire attack class by construction rather than by trying to sanitize the input.

The plan-reuse benefit comes along for free once you're already separating structure from values: since the compiled plan is keyed to the query's structure, not its specific parameter values, the SAME compiled plan can serve many executions with different bound values, avoiding the real cost of re-parsing and re-planning identical query shapes over and over.

Worked example. A parameterized lookup like SELECT * FROM users WHERE email = ? (or the named-placeholder equivalent for your driver of choice), with the actual email value bound separately, is safe against an attacker supplying a value like ' OR '1'='1 as the email, since that string is bound as a literal value to compare against the email column, never re-parsed as SQL; the equivalent string-concatenated query would instead execute a completely different, attacker-controlled query.

Trade-offs and pitfalls. The plan-reuse benefit is exactly what creates the parameter-sniffing risk discussed elsewhere in this topic: reusing one compiled plan across very different parameter values is usually a net win, but for a column with a highly skewed value distribution it can occasionally produce a plan mismatched to the current execution's actual selectivity, which is a genuine (if usually smaller) cost against the much larger, non-negotiable security benefit of avoiding string-concatenated SQL in the first place.