Query Optimization and Execution Plans Questions

Direct answer. Even with a matching index available, a planner can rationally prefer a sequential scan for several reasons: the predicate isn't actually selective enough for the specific data distribution, the statistics are stale, the query is wrapped in a way that defeats the index (a function or type mismatch on the indexed column), the index simply doesn't cover this particular query shape, or the table is small enough that a scan is cheaper regardless.

Structured elaboration.

1. Low selectivity: if the predicate matches a large fraction of the table (say, more than 5-15%, depending on the engine's cost model), a scan that reads pages sequentially can beat an index scan that does many scattered lookups, even though the index scan visits fewer ROWS. Check this by running a plain COUNT(*) with the same predicate and comparing it to the table's total row count.
2. Stale statistics: if the optimizer's stored row-count estimate for the predicate is wrong (too high), it may believe the predicate is unselective even though it currently isn't. Check this by comparing EXPLAIN's estimated rows to EXPLAIN ANALYZE's actual rows for the same predicate.
3. A non-sargable predicate: wrapping the indexed column in a function, or comparing it to a value of a different type that forces an implicit cast, can make the index unusable for that specific query even though the index itself is fine. Check the WHERE clause literally for any function call or cast around the column.
4. The index doesn't match the query: if the index is on a different column, or a composite index whose leading column isn't part of this query's predicate, it can't help this query even though it looks superficially related. Check the exact columns and column order of the existing index against the query's actual predicate.
5. Table (or matching subset) is genuinely small: below some row-count threshold, the fixed overhead of an index lookup exceeds the cost of just reading everything, so a scan is the objectively correct choice and nothing is actually wrong.

Worked example. For a 50-million-row table where a predicate matches roughly 1% of rows (500,000 rows), that's usually still selective enough that a correctly-matching, non-wrapped index should win; if the planner is still choosing a scan in that situation, reasons 2, 3, or 4 above are the most likely explanations, and reason 1 (genuinely low selectivity) is the least likely at that match rate.

Trade-offs and pitfalls. It's tempting to treat "the planner chose a scan" as automatically wrong and reach immediately for a hint to force the index; check the five reasons above first, since forcing an index that the optimizer correctly avoided (because it truly is more expensive for this query) makes things worse, not better.

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. Confirm the OR is defeating index usage by checking whether each branch, on its own, would use an index efficiently but the combined OR forces the optimizer to consider matches from either branch and fall back to a scan; the fix is to rewrite the OR as a UNION ALL of the individual branches, each independently indexable, or an IN-list where the branches are all equality checks on the same column.

Structured elaboration. Many optimizers can use an index efficiently for a single equality or range predicate, but a compound OR across DIFFERENT columns (or a mix of predicate shapes) often can't be satisfied by walking a single index in one pass, since the matching rows for each branch could live anywhere relative to each other in that index's ordering. Rewriting the OR as a UNION ALL of separately-filtered queries lets each branch use its own most-appropriate index independently, with the results combined afterward, rather than forcing one combined scan to somehow satisfy both conditions at once. When every branch of the OR is an equality check on the SAME column, an IN-list is the more natural and equally effective rewrite instead of a UNION ALL.

Worked example. I verified this rewrite preserves results exactly with a small dataset of three events (click, view, purchase) filtered for (type = 'click' OR type = 'view') AND created_at > '2025-01-01':

-- OR across the same column: index usage depends on the optimizer's transform ability
SELECT * FROM events
WHERE (type = 'click' OR type = 'view') AND created_at > TIMESTAMP '2025-01-01';

-- explicit UNION ALL: each branch can use its own index independently
SELECT * FROM events WHERE type = 'click' AND created_at > TIMESTAMP '2025-01-01'
UNION ALL
SELECT * FROM events WHERE type = 'view'  AND created_at > TIMESTAMP '2025-01-01';

Both forms return the same two matching rows (the click and view events, correctly excluding the purchase event), confirming the rewrite is correctness-preserving for this predicate shape.

Trade-offs and pitfalls. UNION ALL, not UNION, is the correct rewrite whenever the original OR's branches can't produce overlapping duplicate rows (as here, since a row can't simultaneously be type='click' and type='view'); using UNION instead would silently add an unnecessary deduplication pass. When branches CAN overlap and produce genuine duplicates under a UNION ALL rewrite, you need either a UNION (accepting its dedup cost) or an explicit condition making the branches mutually exclusive.

Complexity

This rewrite doesn't change the total rows scanned in the worst case, but it changes each branch from a potential full scan to an independent, appropriately-indexed lookup, which is where the real savings comes from.

Edge cases

If the OR's branches can produce the exact same row (for example, an OR across ranges on the SAME column that overlap), a UNION ALL rewrite would duplicate that row in the output; verify the branches are genuinely mutually exclusive before choosing UNION ALL over UNION.

Direct answer. A REGEXP_CONTAINS scan with no date filter against a 5 TB unpartitioned table would need to read the entire dataset regardless of how few rows actually match, at an assumed illustrative rate of $5 per TB scanned that's roughly $25 for a single run of this one query (check your actual account's current rate card, since exact per-TB pricing varies by engine, region, and over time); partitioning by date and adding a date filter, or precomputing a boolean flag so the regex only has to run once (at write time) instead of on every read, would each cut that cost roughly in proportion to how much of the 5 TB the rewritten query actually needs to touch.

Structured elaboration. In a bytes-scanned billing model, the cost of a query is essentially independent of how selective its WHERE clause is UNLESS that WHERE clause can be resolved through partition or clustering metadata before reading the underlying data; a regex applied over an unbounded date range with no partition filter forces every byte of every column referenced (here, the payload column across the entire table) to be read and evaluated, regardless of how rare the matching rows actually are. The two effective levers, in order of how directly they attack the actual cost driver: adding a date filter against a date-partitioned version of the table, so the engine can skip entire partitions outside the query's range before reading anything, cutting bytes scanned roughly in proportion to what fraction of the full date range the query actually needs; and precomputing a boolean has_error flag at write/ingestion time (running the expensive regex once, when the row is written, rather than once per query execution), so read-time queries filter on a cheap boolean instead of re-evaluating a regex over the full payload column every single time.

Worked example. If the query genuinely only needs, say, the last 7 days out of roughly two years of retained data, and that 7-day slice is a representative roughly 1% of the table's total date range, partitioning and filtering to just that range would cut bytes scanned from the full 5 TB to roughly 50 GB; at that same assumed $5/TB rate that's a drop from about $25 per run to about $0.25 per run, a rough two-orders-of-magnitude reduction, purely from letting partition pruning skip the other 99% of the date range before reading it at all. Precomputing the boolean flag stacks on top of that: it removes the need to scan and evaluate the (often large) payload text column at query time altogether for the filtering step, leaving only whatever narrower set of columns the query actually needs to return.

Trade-offs and pitfalls. These numbers are illustrative, not audited against this specific engine's real billing edge cases (minimum billable scan sizes, metadata overhead, and caching behavior all vary by system and can shift the exact dollar figure); treat the ROUGH MAGNITUDE of the improvement (an order-of-magnitude-plus reduction from restricting the date range, an additional meaningful reduction from avoiding a full-column regex re-scan) as the reliable takeaway, and verify the exact billed figure against your specific account and query before reporting a precise number to a stakeholder.

Complexity

Bytes scanned drops roughly in proportion to (fraction of the date range actually queried) once partition pruning applies, and drops further once the expensive column (or the expensive regex evaluation over it) is removed from the read path entirely.

Edge cases

A minimum billable scan size or fixed per-query overhead, present in some billing models, means very small, highly-pruned queries don't necessarily scale their cost all the way down to zero linearly; check your specific engine's billing documentation for that floor before promising a stakeholder a cost reduction proportional all the way down.

Direct answer. Confirm a spill from the plan's own reporting (temp bytes or files, or an explicit spill indicator on the aggregate node) rather than guessing from total time alone; then reduce it by shrinking the input before the aggregate, raising the memory budget for the operation, or accepting the trade-off explicitly if neither of those is practical right now.

Structured elaboration. A hash-based GROUP BY or DISTINCT builds an in-memory structure keyed by the group values; when the number of distinct groups (or the memory needed to track them) exceeds the operation's memory budget, the engine partitions the work and spills intermediate state to disk, processing it in passes, similar in spirit to a spilling hash join. Confirm this specifically happened, rather than assuming, by checking the plan's own reporting: most engines' detailed EXPLAIN ANALYZE output will show temp file or spilled-byte counts directly on the aggregate node when this occurs, and a node whose actual time is dramatically higher than its row counts alone would suggest is a secondary clue worth cross-checking against that direct signal.

Remediation options, roughly cheapest to most invasive: raise the memory setting available to that operation, if the environment has headroom, which is the fastest fix but shares the same "costs something for every concurrent query" trade-off as raising memory for a spilling join; reduce the number of distinct groups or the row count feeding the aggregate with an earlier filter, if the query's actual need allows it; or restructure the aggregation into stages, computing partial aggregates on smaller chunks first (a pre-aggregation or two-phase aggregation pattern) if the full-precision, single-pass aggregate genuinely can't be avoided at this data volume.

Worked example. A GROUP BY product_id over a billion rows, with a genuinely huge number of distinct products, spilling under a memory setting sized for a much smaller, more typical grouping key is a realistic production scenario; if the report only truly needs a coarser grouping (by product CATEGORY rather than individual product), pre-aggregating to that coarser level first, before further processing, can shrink the group count enough to avoid the spill entirely rather than just tolerating it.

Trade-offs and pitfalls. Raising memory limits broadly to accommodate one heavy aggregation query risks starving concurrently-running queries of the same shared resource; where the aggregation genuinely needs high per-query memory regularly, isolating that workload (a separate resource pool, a dedicated time window, or a separate reporting replica) is often a more sustainable fix than a blanket memory increase for every connection.