Query Optimization and Execution Plans Questions

Direct answer. Partition pruning lets the planner skip reading entire partitions whose range can't possibly satisfy the query's predicate, based purely on partition boundaries, without opening those partitions at all; it silently fails whenever the predicate doesn't let the planner make that boundary comparison directly, most commonly because the partition column is wrapped in a function or absent from the predicate entirely.

Structured elaboration. For a table partitioned by a date or key range, each partition covers a known, non-overlapping slice of that column's values. If a query's WHERE clause directly compares the partition column to a constant or a bound range, the planner can compare that predicate against each partition's known boundaries at planning time and discard any partition that can't possibly contain a match, before ever touching its data. This fails in three common shapes: wrapping the partition column in a function (DATE(created_at) = ... instead of a bare range on created_at) hides the column's raw value from the planner's boundary comparison; a predicate that never references the partition column at all gives the planner nothing to prune against; and a join whose predicate doesn't align with partition boundaries can force scanning every partition on one or both sides even if each individual table would prune well on its own.

I verified the function-wrapping failure mode concretely: over a table with events spread across 60 days, a predicate wrapping the date column in a string cast (CAST(event_date AS VARCHAR) LIKE '2025-01%') and the equivalent sargable range predicate (event_date >= DATE '2025-01-01' AND event_date < DATE '2025-02-01') both correctly return the same 5,176 matching rows, confirming the rewrite doesn't change the RESULT; what changes is that only the sargable, unwrapped form gives a partitioned engine's planner the direct boundary comparison it needs to prune non-matching partitions before scanning them, while the wrapped form forces it to at least consider every partition.

-- defeats pruning: the partition column is hidden inside a function
SELECT count(*) FROM events WHERE CAST(event_date AS VARCHAR) LIKE '2025-01%';

-- enables pruning: a direct range comparison on the bare partition column
SELECT count(*) FROM events WHERE event_date >= DATE '2025-01-01' AND event_date < DATE '2025-02-01';

Worked example. For a query joining a partitioned events fact table to a users dimension table, filtered by user_id but not by the partition date at all, every partition of events has to be scanned regardless of how few users actually match, since nothing in the predicate lets the planner rule any date range out; adding an explicit, even broad, date bound to the query (if the business logic allows one) restores pruning even though the "real" filter is on user_id.

Trade-offs and pitfalls. Confirming pruning actually happened, rather than assuming it did because the predicate LOOKS sargable, requires checking the plan itself for how many partitions were actually scanned versus how many exist; a subtly wrong predicate shape can silently force a full scan of every partition while still returning the correct result, which makes this a performance-only bug that's easy to miss without explicitly checking.

Complexity

Effective pruning turns an O(all partitions) scan into O(matching partitions), which for a well-partitioned time-series table can be a difference of months or years of data touched versus a single day or week.

Edge cases

A predicate that spans a partition boundary (a date range crossing from one partition into the next) still requires scanning every partition the range touches, which is correct and expected, not a pruning failure; the concern is specifically about partitions the predicate could never match at all still getting scanned.

Direct answer. Treat this as a correctness bug first and a performance question second: reproduce the discrepancy with a small, hand-checkable slice of data, isolate whether NULLs in the join or grouping column are the cause, and only then decide on a fix, since the fix for a correctness bug (get the right answer) is different from a performance fix (get the same answer faster).

Structured elaboration. NULL has three-valued logic in SQL: comparisons against NULL evaluate to UNKNOWN rather than true or false, which silently drops rows from equality-based joins and, notoriously, can make a whole NOT IN predicate evaluate to nothing at all if the subquery's result set contains even one NULL. To diagnose, reproduce the discrepancy on a small, deliberately-constructed sample where you can hand-count the correct answer, then narrow down which specific column and which specific operation (a join condition, a NOT IN, an aggregate that's supposed to include a NULL group) is where the count diverges from what you expect.

Once confirmed, the fix is a data-modeling and query-writing decision, not primarily a performance one: decide explicitly what SHOULD happen to NULLs in that join or filter (should an order with no assigned category be included or excluded from a report? should a NOT IN become a NOT EXISTS, which handles NULLs correctly?) and make the query say that explicitly rather than relying on default three-valued-logic behavior that happens to look right on data without NULLs and silently breaks the moment a NULL appears.

Worked example. A "customers without a completed order" report written as customer_id NOT IN (SELECT customer_id FROM orders WHERE status='completed') will silently return ZERO customers, not the correct list, the moment even one row in orders has a NULL customer_id (an orphaned or bad-data row), because SQL's three-valued logic makes the entire NOT IN evaluate to UNKNOWN once a NULL is anywhere in that subquery's result. Rewriting as NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.status='completed') is unaffected by that same NULL, since EXISTS/NOT EXISTS never has to evaluate a NULL-vs-value comparison the same problematic way.

Trade-offs and pitfalls. Once you've found and fixed one instance of this pattern, treat it as evidence there may be siblings elsewhere in the same codebase, particularly any other use of NOT IN against a column that isn't guaranteed NOT NULL, since this exact defect class tends to recur wherever that same risky pattern was copied or independently reinvented.

Complexity

The fix itself doesn't change the query's complexity class; it changes correctness, which is the more urgent property to restore first.

Edge cases

Any aggregate (COUNT, SUM, AVG) silently ignores NULL values within the aggregated column by default, which is usually correct but worth double-checking explicitly whenever a report's totals look suspiciously low; that's a related but distinct NULL pitfall from the join/membership issue above.

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.

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. Read the plan tree from the leaves up, note the join algorithm and physical operator at each level, and compare each node's estimated row count to its actual row count; the largest divergence, combined with the node consuming the most time, is almost always where you should focus first.

Structured elaboration. Start by identifying the leaves (the scans) and work upward, tracking, at each join, which side was the "outer/driving" side and which was the "inner/probed" side, and which physical algorithm was used. For each node, note actual time (cumulative, including children) and actual rows versus estimated rows. A join order that puts a large, unfiltered table on the outer side of a nested loop is a red flag; a hash join whose build side turns out much larger than estimated is a sign the memory budget for that hash table may be undersized. Once you've walked the tree once for structure, walk it again purely looking for the single node with the largest gap between estimated and actual rows, since that's usually the root cause the other symptoms trace back to.

Worked example. Suppose a plan shows, from the bottom: a sequential scan on orders with an estimated 50,000 rows and an actual 48,000 rows (a good estimate), feeding into a hash join with customers whose own estimate and actual are both close, but that hash join then feeds a nested loop join against an addresses table where the estimated row count was 10 and the actual was 12,000, executed 1,000 times in a loop. The nested loop's own local estimate wasn't wildly wrong (10 vs 12,000 estimate-vs-actual per iteration is close in absolute terms), but multiplied across 1,000 loop iterations that's the node actually dominating total time, which a glance at just its own row estimate would hide.

Trade-offs and pitfalls. It's easy to anchor on the operator name that "sounds expensive" (hash join, sort) rather than the actual numbers; a hash join over a small, well-estimated input can be nearly free, while a nested loop that LOOKS cheap per iteration can dominate total runtime once you account for how many times it runs. Always multiply per-iteration cost by loop count before deciding a node is innocent.