Query Optimization and Execution Plans Questions

Direct answer. Selectivity is the fraction of a table's rows a predicate matches; cardinality is the actual number of rows that come out of an operation. A highly selective predicate (one matching few rows) is exactly the situation where an index earns its cost, and predicate selectivity is also the main input the optimizer uses to decide join order, since it wants to apply the most narrowing filters as early as possible.

Structured elaboration. If a predicate's selectivity is 1% (it matches roughly 1 in 100 rows), an index lookup that jumps straight to those rows beats scanning the whole table, since the fixed cost of the index lookup is repaid many times over by the rows it lets you skip; a predicate matching 50% of rows offers much less benefit from an index, since you'd be visiting half the table's rows either way, just via a more expensive access pattern. For join order, the optimizer generally wants to apply the predicate (or combination of predicates) that will shrink the working set the most, as early in the join sequence as possible, since every table joined afterward then only has to be matched against a smaller intermediate result rather than the full one.

To estimate a predicate's selectivity from existing statistics without running the full query, you'd typically look at the number of distinct values for that column (assuming a roughly uniform distribution, selectivity for an equality predicate is close to 1 divided by the distinct value count) or, where available, a stored histogram that captures the actual distribution more precisely for skewed columns.

Worked example. For a users table with a country column where 90% of rows are 'US' and the remaining 10% are spread across 40 other countries, filtering country = 'US' has low selectivity (matches ~90% of rows, an index doesn't help much, a scan is likely the right choice), while filtering country = 'Luxembourg' has high selectivity (matches a tiny fraction, an index on country earns its cost here even though it's the SAME column and the SAME index).

Trade-offs and pitfalls. Selectivity isn't a fixed property of a column, it's a property of a specific predicate VALUE against that column's distribution; assuming one index is uniformly "good" or "useless" for a column without accounting for which specific values are queried most is a common estimation mistake, and it's exactly why skewed columns need better-than-average statistics (histograms or multi-value frequency counts) to plan well.

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. Capture a baseline under realistic, steady-state conditions; run enough warm-up iterations to remove cold-cache noise, and enough repeated measurements to distinguish a real improvement from run-to-run variance; then compare the SAME set of metrics (not just wall-clock time) before and after, under matching concurrency, before calling the result statistically meaningful.

Structured elaboration. A fair baseline needs to reflect the workload's actual steady-state cache behavior, not an artificially cold cache (first run after a restart) or an artificially warm one (running the same query in a tight loop with nothing else happening), since either extreme can make a change look better or worse than it would in production. Run a handful of warm-up iterations before recording anything, then take enough repeated measurements to compute a distribution (not a single number), since a single before/after pair can't distinguish "genuinely faster" from "happened to catch a quiet moment." Collect more than wall-clock time where possible, buffer/IO statistics, CPU time, and (for a rewrite specifically) the plan shape itself, since two runs with similar wall-clock time but very different plan shapes tell you something different than two runs with the same plan shape and different timing. Match concurrency between the before and after runs; testing a rewrite in isolation and comparing it to a baseline captured under real concurrent load (or vice versa) invalidates the comparison.

Worked example. For a proposed query rewrite, a reasonable protocol is: run the original ten times (after three untimed warm-up runs), run the rewrite ten times under the same conditions, and compare the DISTRIBUTIONS of both wall-clock time and a secondary metric like buffer reads, rather than comparing single best-case or single arbitrary runs against each other; if the rewrite's distribution is meaningfully and consistently better across secondary metrics too (not just occasionally faster by chance), that's much stronger evidence than one favorable timing.

Trade-offs and pitfalls. It's tempting to declare victory off a single favorable before/after pair, especially under time pressure; resist that, since a single pair can't distinguish signal from noise, and a change that looked good in one measurement but doesn't hold up under repeated, matched-condition testing is exactly the kind of false confidence that erodes trust in benchmarking as a practice going forward.

Direct answer. Walk through your triage process for narrowing down what changed: what evidence you would gather first, the handful of underlying causes that pattern is usually explained by, and how you would confirm which one actually happened rather than guessing.

Structured elaboration. Start by gathering evidence that distinguishes "the plan changed" from "the plan is the same but the work it does grew": compare the current plan to a known-good baseline if one is available (many engines let you capture and diff plans, or at minimum you can re-run EXPLAIN and eyeball the operator shapes), and check whether the DATA changed (row count growth, a new skewed value, a bulk load) independent of any plan change. From there, the most common root causes for this "regressed with no application change" pattern are, in roughly descending order of how often they turn out to be the actual cause: statistics that went stale after a data change (a bulk load or a big delete that wasn't followed by a refresh); a cached plan compiled for an atypical parameter value now being reused for a much more typical, much less selective one (a parameter-sniffing regression); the DATA itself simply growing or shifting distribution past a threshold that used to favor one strategy and now favors another; and resource contention from something unrelated to this specific query (concurrent load, a maintenance job, disk pressure) that's slowing everything down and only looks query-specific because that's the query someone happened to notice first.

To confirm which one actually happened rather than just picking the most common cause and hoping: check statistics freshness and the estimated-vs-actual row counts at the plan's key nodes first, since that single check distinguishes the stale-statistics and parameter-sniffing causes (both would show a mismatch) from the pure-resource-contention cause (which typically wouldn't); if estimates and actuals both look reasonable and current, pivot to checking system-level resource metrics (CPU, I/O, lock waits) for the affected time window instead.

Worked example. A nightly report that quietly regressed after last week's data load added a disproportionate number of rows in one previously-small category is a textbook case: the statistics captured before the load still describe the smaller, more balanced distribution, so the optimizer's row-count estimate for that category is now badly wrong, and a plan built around that bad estimate (likely favoring an index-heavy strategy suited to the old, smaller volume) is now handling a much larger actual volume than it was ever appropriate for.

Trade-offs and pitfalls. It's tempting to jump straight to "just refresh statistics and see if that fixes it" without confirming that's actually the cause; that's a reasonable first cheap experiment to try, but treat a fix that "seems to help" with some skepticism until you've actually looked at the estimated-vs-actual gap directly, since a coincidental unrelated change (contention easing off on its own) can make an unrelated fix look like it worked.

Direct answer. Frame it in terms they already care about (report speed, or the cost of the current slow experience), name the concrete cost honestly (the write-latency or storage number, not a vague "some overhead"), and commit to a specific way you'll confirm afterward that it actually delivered the promised benefit.

Structured elaboration. Lead with the business-visible symptom and its fix, not the technical mechanism: "this report currently takes X seconds and blocks people from Y; this change gets it to Z seconds" lands better than opening with index internals. State the cost in the same concrete terms: a specific percentage increase in write latency, or a specific amount of additional storage, rather than a hedge like "a little overhead," since a specific number is both more honest and more persuasive precisely because it shows you've actually measured it rather than guessing. Explain briefly WHY the cost exists (every write now has to update this extra structure too) so the trade-off feels like a reasoned decision rather than a black box. Close with how you'll verify the benefit actually materialized after shipping, a specific metric you'll check and a specific timeframe, so the stakeholder isn't just taking your word for it.

Worked example. "Right now this report takes the better part of a minute to load, and three people have told me they've stopped using it because of that. Adding this index gets it to load near-instantly, based on my testing. The cost is a roughly 10% increase in write latency for this specific table, workload testing suggests that stays well within our current write-latency budget, and I'll check actual production write latency and report load time again a week after we ship, to confirm both numbers hold up outside of my test environment, not just in it."

Trade-offs and pitfalls. Resist the urge to round the cost down or bury it in technical language to make the ask easier to approve; a stakeholder who later discovers an unmentioned cost, even a small one, loses trust in every future recommendation you bring them, which is a much larger cost than the discomfort of stating the trade-off plainly up front.