Query Optimizer Failure Modes and Edge Cases

Catastrophic Plan Selection: The most dangerous production incidents come not from missing indexes but from the optimizer choosing a catastrophically bad plan. The root cause is usually poor cardinality estimation. If statistics are stale or simplistic, the optimizer might believe a filter is highly selective when it actually matches millions of rows. Consider a query joining users to orders with a filter on order_status = 'pending'. If statistics from last month show 1% of orders are pending (10,000 rows), the optimizer might choose a nested loop join, reading 10,000 rows from orders and doing 10,000 index lookups into users. This takes 100 milliseconds. But if pending orders grew to 40% (4 million rows) due to a processing backlog, that same nested loop join now does 4 million index lookups taking over 60 seconds p99. The query itself did not change. The data distribution changed, but stale statistics misled the optimizer. Data Skew in Distributed Systems: Distributed query engines partition work across multiple workers, often using hash distribution on join keys. If the join key is highly skewed, disaster follows. Imagine joining customer orders where one enterprise customer accounts for 40% of all orders. A hash shuffle sends all their orders to a single worker. That worker becomes a straggler processing 40% of data while others finish in seconds. Overall query latency becomes 5 to 10 times higher than expected. Systems like Spark SQL implement skew mitigation by duplicating small tables or splitting heavy keys, but these are heuristics that don't always trigger. Production queries against skewed data can see p95 latency of 45 seconds when p50 is 6 seconds, purely due to one hot partition. Parameter Sniffing and Plan Caching: OLTP databases like SQL Server and PostgreSQL cache compiled plans to avoid recompilation overhead. This creates a subtle failure mode called parameter sniffing. The first execution compiles a plan optimized for its specific parameter values. Subsequent executions reuse that plan even with different parameters. If the cached plan was compiled for customer_id = 123 (a customer with 5 orders), it might choose index lookups. When the same query runs with customer_id = 999 (an enterprise customer with 500,000 orders), it still uses index lookups when a full scan would be faster. Latency jumps from 10ms to 8 seconds for that one parameter value. The fix requires plan cache invalidation or query hints, neither of which is automatic. Queries That Defeat Optimization: Certain query patterns make optimization impossible. Applying functions to indexed columns like WHERE LOWER(email) = '[email protected]' prevents index usage because the index stores original values, not lowercased ones. The optimizer must fall back to full table scans. Similarly, complex OR conditions across non aligned columns, such as WHERE (status = 'active' AND region = 'US') OR (priority > 5 AND created_date > '2024-01-01'), split predicate pushdown and often force multiple scans. User defined functions (UDFs) are opaque to the optimizer. It cannot estimate their selectivity or cost. A UDF that looks cheap might actually make an HTTP call taking 200ms per row. Applying this to 1 million rows turns a 2 second query into a 55 hour query. Always avoid row by row UDFs in hot paths.