Query Optimization and Execution Plans Questions

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. Confirm it by checking the WHERE clause literally for a function call or type cast wrapped around the indexed column; the fix is to rewrite the predicate so the indexed column appears bare on one side of the comparison, moving any transformation to the constant instead.

Structured elaboration. An index on a column can only be used efficiently by predicates the engine can translate directly into a range or equality scan of that index's stored values. The moment the column itself is wrapped in a function (a date-extraction function, a case-normalization function like lower()) or compared to a value of a different type that forces an implicit conversion, the engine generally can no longer map the predicate onto the index's stored order and has to fall back to evaluating the function for every row, which usually means a full scan. The confirmation step is mechanical: look at the WHERE clause and ask "is the indexed column, unmodified, on one side of a comparison operator?" If not, that's very likely the cause, and EXPLAIN will typically confirm it by showing a scan rather than the expected index usage.

The fix comes in two shapes. Query-side: rewrite the predicate so the column is bare and any transformation moves to the constant side of the comparison, for example turning an equality-on-a-truncated-date into a range comparison against the bare timestamp column. Schema-side, when a query-side rewrite genuinely isn't possible (the transformation is fundamental to the business logic, like case-insensitive matching), create an expression index that stores the transformed value directly, so the index itself already reflects lower(email) and the query can match against it.

Worked example. I verified the query-side rewrite is correctness-preserving with a small dataset: a query filtering CAST(order_ts AS DATE) = DATE '2025-01-01' (non-sargable, wraps the column) against four rows returns order_ids 1 and 3; rewriting to the equivalent range form returns the identical set.

-- non-sargable: function wraps the indexed column, defeats the index
SELECT order_id FROM orders
WHERE CAST(order_ts AS DATE) = DATE '2025-01-01';

-- sargable rewrite: bare column, range comparison against two constants
SELECT order_id FROM orders
WHERE order_ts >= TIMESTAMP '2025-01-01'
  AND order_ts <  TIMESTAMP '2025-01-02';

Both return order_id 1 and 3 for a table with rows at 2025-01-01 10:00, 2025-01-02 09:00, 2025-01-01 23:59:59, and 2025-02-01 00:00, confirming the rewrite changes only the execution strategy, not the result.

Trade-offs and pitfalls. The rewrite has to be exactly semantically equivalent, not just "close": an off-by-one on the upper bound (using <= against the start of the next day instead of < the next day) would silently include an extra midnight row. When an expression index is the only realistic fix, remember every query that wants to benefit from it must use the exact same expression the index was built on; a query written slightly differently (a different function, or the same function with different argument order) won't match.

Complexity

The rewrite doesn't change the query's asymptotic complexity by itself, it changes whether an O(log n) index lookup or an O(n) scan is even available as an option.

Edge cases

Time-zone-aware timestamp columns need extra care: a naive date-range rewrite can shift results by the UTC (Coordinated Universal Time) offset if the column and the literals aren't in the same time zone convention. Boundary values exactly at midnight need the range's inclusive/exclusive ends checked carefully against the original semantics.

Direct answer. EXPLAIN shows you the plan the optimizer WOULD use and its estimated costs, without running the query. EXPLAIN ANALYZE actually executes the query and reports both the estimates and the real, observed numbers (actual rows, actual time, loop counts) side by side, which is what lets you see where the optimizer's model of the data was wrong.

Structured elaboration. Because EXPLAIN alone doesn't execute anything, it is safe to run against any query, including a write query or one you suspect might be catastrophically slow, and it costs essentially nothing. EXPLAIN ANALYZE actually runs the query end to end (some databases support a dry-run or rollback mode for writes, but by default assume it executes), so it takes as long as the query itself and has the query's real side effects. You reach for EXPLAIN ANALYZE specifically when you need the actual numbers, most commonly to compare "estimated rows" against "actual rows" at each node: a large gap there is one of the single strongest signals that statistics are stale, a predicate is more selective or correlated than the optimizer assumed, or a data distribution has shifted.

Worked example. For a query that filters on created_at in the last 7 days, EXPLAIN might estimate 5,000 matching rows from an index scan. Running EXPLAIN ANALYZE against the same query might show the same index scan node actually returning 500,000 rows. That gap (5,000 estimated vs. 500,000 actual) tells you the optimizer under-costed this branch of the plan and likely chose a downstream join algorithm (say, a nested loop) that only makes sense for the small estimate, not the real volume, which is a very different diagnosis than "the index scan itself is slow."

Trade-offs and pitfalls. Running EXPLAIN ANALYZE against an expensive query in production has a real cost: it fully executes the query, so a query that would normally time out or that writes data is not safe to blindly EXPLAIN ANALYZE without thinking about it first (a SELECT read against a replica, or wrapping a write in an explicit transaction you roll back, are common ways to make this safe). It's also worth remembering that EXPLAIN ANALYZE's timing numbers include the overhead of instrumentation itself, so absolute numbers can be slightly inflated versus a bare run of the query; the RELATIVE comparison between nodes, and between estimated and actual, is what actually matters.

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. Parameter sniffing happens because a compiled, cached plan is built using the specific parameter value it first saw, and that plan's shape (which join algorithm, which access method) is only optimal for parameter values with a similar selectivity; a wildly different parameter value reused against that same cached plan can get a badly mismatched strategy instead of a plan tailored to it.

Structured elaboration. Compiling a plan is genuinely expensive, so reusing a cached plan across executions with different parameter values is usually the right default, since recompiling every single execution would cost more in aggregate than occasionally running a slightly suboptimal cached plan. The problem is specifically when the DISTRIBUTION of a filtered column is skewed: a plan compiled for a parameter value that matches very few rows (favoring an index-heavy, nested-loop-friendly strategy) can be catastrophically wrong when reused for a parameter value that matches a huge fraction of the table (which would have wanted a scan-and-hash-join strategy instead), and vice versa.

Three mitigation strategies, with their own trade-offs: (1) recompiling for specific problematic queries (an explicit "always compile fresh for this statement" hint) trades the compilation cost, paid every time, for guaranteed plan-appropriateness, which is worth it for a query executed rarely enough that compilation cost is a rounding error, and wrong for one executed thousands of times a second where that cost would dominate; (2) compiling against a deliberately "average" or "unknown" assumption about the parameter, rather than the literal first-seen value, trades away plan-appropriateness for EITHER extreme in exchange for a plan that's reasonably okay across the whole range, which suits a column whose skew isn't too extreme; (3) maintaining multiple cached plans keyed by parameter-value bucket (where the engine supports it) gets closer to "the right plan for the right value" without paying full recompilation cost every time, at the cost of more cache memory and more operational complexity to reason about.

Worked example. A stored procedure looking up orders by status, where 95% of rows are 'completed' and 1% are 'disputed': a plan first compiled and cached while looking up 'disputed' orders (an index-heavy plan, since that value is highly selective) reused later for 'completed' orders would try to walk an index for what's now 95% of the table, dramatically slower than the scan-based plan that value actually deserves.

Trade-offs and pitfalls. There's no universally correct default among the three mitigations above; the right one depends on how skewed the underlying column actually is, how often the query executes, and how much compilation overhead the workload can tolerate, which is exactly why this remains a judgment call rather than something to fix once and forget.