Query Optimization and Execution Plans Questions

Direct answer. IN and EXISTS both test membership but differ in how NULLs interact with them and in how each maps onto a join in the optimizer's mind; JOIN differs from both in that it's row-producing rather than boolean, so it only behaves like a membership test if the inner side is guaranteed unique per outer row.

Structured elaboration. WHERE x IN (subquery) is true if x matches any row the subquery returns; critically, if the subquery's result set contains even one NULL and x doesn't match any non-NULL value, the whole expression evaluates to UNKNOWN rather than false, which under NOT IN specifically can silently make the entire outer predicate evaluate to nothing at all (a classic, expensive-to-debug correctness trap, not just a performance one). WHERE EXISTS (correlated subquery) instead asks only "does at least one matching row exist," is unaffected by NULLs in the same way, and lets the engine short-circuit as soon as one match is found rather than materializing a full list to compare against. A plain JOIN used for the same membership-testing purpose can silently change the row count of the outer query if the inner side isn't unique per join key, duplicating outer rows once per match, which neither IN nor EXISTS does since they only ever return true or false.

The optimizer is often free to transform one of these into another internally, when they happen to be logically equivalent for that specific query, so the choice between them is not always a performance decision. Where it genuinely matters, EXISTS's short-circuit behavior tends to help most when only a small fraction of outer rows actually have any match, and its immunity to the NULL trap makes it the generally safer default whenever the inner column can contain NULLs.

Worked example. For customers where you want those with at least one high-value order, IN (SELECT customer_id FROM orders WHERE amount > 1000) and EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.amount > 1000) return identical results as long as customer_id in orders never contains NULL; if it can be NULL and you instead wrote NOT IN (...) to find customers WITHOUT a high-value order, that query can silently return zero rows the moment even one NULL customer_id exists in orders, while the equivalent NOT EXISTS form is unaffected.

Trade-offs and pitfalls. Prefer EXISTS/NOT EXISTS over IN/NOT IN whenever the inner column isn't guaranteed NOT NULL, purely for correctness, independent of any performance difference; and never reach for a plain JOIN as a membership test unless you've confirmed the inner side is unique per join key, or wrap it in a DISTINCT to restore the semantics you actually wanted.

Complexity

All three can, in principle, execute as a semi-join internally when the engine recognizes the pattern, so there is often no fundamental asymptotic difference; the practical differences come from NULL handling and from whether the engine actually recognizes the equivalence for a given query shape.

Edge cases

NOT IN against a column that can contain NULL is the single highest-risk pattern here and deserves an explicit check (or an automatic ban) in code review, since it fails silently rather than with an error.

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. The most plausible causes are that the two connections are actually hitting different underlying data (a lagging or differently-tuned replica), that the replica's statistics are stale relative to the primary's, or that a configuration difference between the two (memory settings, cost parameters) leads the same query to a genuinely different plan; isolate which one by comparing statistics freshness, configuration, and data currency between the two connections directly, rather than assuming the query itself is the variable.

Structured elaboration. Start by confirming the two connections are even hitting the same DATA: replication lag means a replica can be seconds, minutes, or more behind the primary, and while that usually doesn't change PLAN shape by itself, it's worth ruling out as a confounding factor before you go further, since a stale replica muddies any comparison you make. Next, compare statistics freshness on the specific tables involved: if the replica's statistics were refreshed at a different time (or on a different schedule) than the primary's, or if statistics don't replicate identically depending on your engine's replication mechanism, the two connections can genuinely be planning from different information even though the underlying schema and data are notionally "the same." Finally, compare configuration directly: a replica provisioned with different memory settings, different cost-model parameters, or even a different engine version can lead an otherwise-identical query to a genuinely different, and genuinely differently-optimal, plan.

Worked example. A replica whose statistics job runs on a different, less frequent schedule than the primary's is a very plausible explanation on its own: if the primary was recently ANALYZE'd after a data shift but the replica hasn't caught up yet, the replica's optimizer is working from an older picture of the data than the primary's, which can easily produce a different (and worse) plan for the identical query text.

Trade-offs and pitfalls. Resist jumping straight to "the replica's hardware must just be slower," which is possible but is a much less common actual cause than a genuine statistics or configuration difference, and treating it as the default explanation without checking the more likely causes first can send you chasing an infrastructure upgrade that wouldn't have fixed the real problem.

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. Predicate pushdown means moving a filter as early as possible in a query's execution, ideally all the way down to the storage layer itself, so the engine reads less data from disk in the first place rather than reading everything and filtering afterward in memory.

Structured elaboration. Against columnar formats like Parquet or ORC, files are typically organized into row groups or stripes, each carrying summary statistics (like min/max values) for every column. If a filter can be pushed all the way down to the storage layer, the engine can skip entire row groups whose min/max range can't possibly satisfy the filter, without even reading their column data off disk, which is a much bigger win than filtering rows after they've already been read into memory. The same principle applies through a foreign data wrapper or a similar external-source connector: pushing a filter down to the remote system means less data crosses the network and less work happens locally, versus pulling everything across first and filtering client-side.

Worked example. Consider a wide, JSON-payload-heavy analytics table where a query filters on a narrow, well-indexed timestamp range and only needs a couple of columns. If the filter and the column selection both get pushed down to the storage layer, the engine reads only the row groups whose timestamp range overlaps the filter, and only the specific columns requested, skipping the (often much larger) JSON payload column entirely; if pushdown fails for some reason (the filter is wrapped in a function the storage layer's statistics can't reason about, for instance), the engine instead reads every row group's relevant columns into memory and applies the filter there, doing far more I/O for the identical logical result.

To verify pushdown is actually happening rather than assumed, check your engine's plan output for an explicit indicator (many engines report something like "filter pushed down" or a bytes-read figure noticeably smaller than the table's full size on disk) rather than trusting that a filter which LOOKS pushdown-friendly actually was.

Trade-offs and pitfalls. Pushdown eligibility is sensitive to exactly how a predicate is written, the same logical filter expressed with a function wrapped around the column, or compared against a mismatched type, can silently fail to push down even though a semantically-equivalent bare-column comparison would have; this is the same underlying sensitivity that defeats sargability for index usage, applied one layer further down the stack.