Query Optimization and Execution Plans Questions

Focuses on diagnosing slow queries and reducing execution cost through analysis of query execution plans and systematic query rewrites. Candidates should be able to read and interpret explain output and execution plans including identifying expensive operators such as sequential table scans index scans sorts nested loop join hash join and merge join and explaining why those operators appear. Core skills include cost and cardinality estimation understanding join order and predicate placement predicate pushdown and selectivity reasoning comparing exists versus in versus join patterns and identifying common anti patterns such as N plus one queries. The topic covers profiling and benchmarking approaches using explain analyze and runtime statistics comparing estimated and actual row counts proposing and validating query rewrites and configuration or schema changes and reasoning about trade offs when using materialized views caching denormalization or partitioning to improve performance. Candidates should present step by step approaches to diagnose problems measure improvements and assess impact on other workloads.

MediumTechnical

65 practiced

Given a slow query where the optimizer chooses a nested loop join but you suspect a hash join would be faster for most parameter values, outline safe experiments you would run to validate this hypothesis in staging, and how you would measure and justify a configuration change or query rewrite in production.

Sample Answer

Approach: run controlled, repeatable experiments in staging to compare nested-loop vs hash-join behavior across a realistic range of parameters, then use statistical metrics and risk-controlled rollout to justify production changes (config or rewrite).

Staging experiments- Prepare realistic environment: use a recent production snapshot or a representative subset on a staging replica; match schema, indexes, stats, and hardware.- Baseline runs: capture current plan and metrics with EXPLAIN (ANALYZE, BUFFERS) (or equivalent). Record wall time, CPU, reads, temp files, buffer hit/miss, and plan nodes.- Parameter sweep: identify the parameter(s) that influence row counts (e.g., filter selectivity) and test a representative sample (e.g., 1%, 5%, 10%, 25%, 50%, 100%). For each value run N repeats (N≥5) to account for variance.- Force join types: for each parameter set, force nested-loop and force hash-join (e.g., set enable_hashjoin = off / enable_nestloop = off in Postgres, or use optimizer hints in other DBs). Compare EXPLAIN ANALYZE outputs.- Cache control: run cold-cache and warm-cache scenarios (drop buffers between cold runs) because IO vs CPU tradeoffs matter.- Concurrency: run experiments under realistic concurrent load if query competes for resources.- Collect summary stats: median, mean, p95 of runtime; IO and CPU cost; temp-space usage.

Analysis & decision criteria- Quantify improvement: require meaningful gains on expected production distribution (e.g., median latency reduced by ≥30% and no regression at p95).- Check stability and edge cases: ensure hash-join doesn’t create excessive temp disk or memory failures for high-selectivity parameters.- Cost/risk assessment: evaluate memory/temp file use, potential spill-to-disk, and impact on other workloads (hash joins consume memory).- Options: (a) add a query rewrite that encourages hash join (reorder joins, use derived tables) or (b) set planner config (e.g., adjust join_costs or enable flags) or (c) use hints if available.

Production rollout- Staged deployment: start with small percentage of traffic (canary) or specific reporting jobs during low-traffic window.- Monitoring: track same metrics (latency percentiles, error/timeout rates, memory/temp usage, system load). Use alert thresholds tied to acceptance criteria.- Rollback plan: automatic rollback if p95 latency increases >10% or if memory/temp usage exceeds safe thresholds.- Documentation: record experiments, assumptions, and owners; include parameter ranges where change is beneficial.

Justification for stakeholders- Provide summarized evidence: representative parameter distributions, before/after percentiles, resource impact, and risk mitigation plan. State trade-offs (e.g., faster typical runs vs higher memory cost) and recommend the safest option (prefer query rewrite if it yields consistent benefits across parameters; otherwise, targeted planner tuning with gradual rollout).

HardTechnical

90 practiced

You plan to create a new index in production to speed a critical query, but past index changes have sometimes caused regressions. Design a safe rollout and rollback plan that minimizes risk: include steps for creating the index non-blocking, validating performance improvements, gradual rollout, monitoring metrics to watch during rollout, and concrete rollback criteria and steps.

Sample Answer

Situation: We need a new production index to speed a critical reporting query, but prior index changes sometimes regressed other workloads.

Plan (safe rollout + rollback):

1. Prep & non-blocking creation- Review query plan and confirm index columns and INCLUDEs with DBA/engineer.- Create index non-blocking: use CREATE INDEX CONCURRENTLY (Postgres) or CREATE INDEX ONLINE (MySQL/InnoDB/SQL Server online option) during low-traffic window.- Take a pre-change snapshot: capture pg_stat_activity, current query plans, execution times, index usage stats, and baseline business metrics (report latency, ETL windows).

2. Validate locally / staging- Apply index in staging on production-sized data; run representative query set and compare EXPLAIN ANALYZE results and end-to-end report times.

3. Canary / gradual rollout- Start by enabling the new index for a small subset of traffic: - If application-level routing possible: route 1–5% of reports/queries to a canary instance using the new index. - If not: run scheduled A/B query runs: execute both old and new query plans in parallel for sample jobs, compare results and timings.- Increase canary to 10%, 25%, 50% over hours/days while monitoring.

4. Monitoring during rollout (automated)- Performance metrics: - Query latency distribution (p50/p95/p99) of critical query - Total report generation time and SLA breaches - CPU, IO, and lock/wait metrics on DB hosts - Index creation/usage stats and planner chosen plan counts - Upstream ETL duration and downstream dashboards refresh times - Error rates and result diffs (row counts, aggregations)- Alert thresholds: - If p95 latency increases >10% vs baseline, or p99 >20% increase - If CPU or IO increases >20% sustained over 5m - Any incorrect result diffs or failing jobs → immediate halt

5. Rollback criteria & steps (concrete)- Immediate rollback if: - Incorrect query results or data discrepancies - Latency/SLA breaches beyond thresholds - Resource saturation causing unrelated workload regressions- Rollback steps: 1. Pause canary traffic immediately (stop routing). 2. If index creation used CONCURRENTLY/ONLINE, DROP INDEX CONCURRENTLY/ONLINE to remove safely. 3. Re-run baseline checks and confirm system back to pre-change metrics. 4. Open incident, document findings, capture profiler traces and EXPLAINs, and run root-cause analysis before reattempt.- If DROP INDEX is slow, coordinate with DBA and stakeholders; consider reverting application-side routing until safe.

6. Communication & governance- Announce schedule and rollback plan to stakeholders and on-call DBAs.- Run a blameless postmortem for any regression and iterate index definition or planner hints.- Only promote to 100% after 48–72 hours of stable metrics and zero data diffs.

Why this works:- Non-blocking creation minimizes availability risk.- Canary/A-B testing isolates impact and catches regressions early.- Concrete metrics and thresholds enable objective rollback decisions.- Coordination with DBAs and staged promotion reduces blast radius.

HardSystem Design

140 practiced

Design a benchmarking harness to compare two variants of a heavy aggregation query. Include how you will prepare the database (priming cache, vacuum/analyze), what metrics to capture, how to simulate concurrent users, how many iterations to run, and how to ensure the comparison is fair and reproducible over time.

Sample Answer

Requirements & goals:- Compare two SQL variants of a heavy aggregation for performance and stability under realistic load.- Produce reproducible, statistically-significant results and actionable diagnostics (plans, resource metrics).

Benchmark steps

1. Environment preparation- Use an isolated, versioned environment (container/VM) with fixed DB version, config, and hardware.- Restore a representative snapshot of production-sized data. Record dataset size and table stats.

2. DB priming & maintenance- Run VACUUM (FULL if safe) and ANALYZE to ensure accurate planner statistics: - psql -c "VACUUM (VERBOSE, ANALYZE) my_schema.my_table;"- Clear OS and DB caches between controlled runs when needed: - To test cold-cache: restart DB/process or drop filesystem cache (e.g., echo 3 > /proc/sys/vm/drop_caches) if permissible. - For warm-cache: run a “priming” query that touches all relevant pages (SELECT COUNT(*) FROM ... WHERE ...).- Record plan before and after with EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) to capture IO/hit details.

3. Metrics to capture- Query-level: wall-clock latency (p50, p90, p95, p99), mean, stddev, success/failure.- Throughput: queries/sec, concurrency vs latency curve.- Resource: CPU %, memory, disk I/O, read/write bytes, buffer hit ratio, temp file usage.- DB internals: actual execution plan, rows processed, planner time, execution time, buffer hits/reads, lock waits.- System metrics: network, context switches, swap usage.- Store all raw traces and explain outputs in timestamped files.

4. Concurrency simulation- Use a load tool that supports SQL (pgbench for Postgres, JMeter/custom Python async workers): - Model realistic user behavior: think times, variable query parameters, and transactions per session. - Run with multiple client threads to achieve target concurrency levels (e.g., 1, 5, 10, 25, 50).- Ensure connection pool settings match production.

5. Iterations & statistical rigor- For each variant and concurrency level: - Warm-up phase: run the query set for 3–5 minutes to stabilize caches/compilation. - Measurement phase: run N independent iterations (N >= 30 recommended) capturing single-query latencies. Alternatively, run fixed-duration load tests (e.g., 5–15 minutes) and sample. - Randomize execution order across variants to avoid temporal bias. - Perform paired statistical tests (paired t-test or Wilcoxon) on per-iteration latencies; report effect size and confidence intervals.

6. Fairness & reproducibility- Keep schema, stats, DB settings, and hardware constant. Version-control the benchmark scripts and query variants.- Use deterministic seeds for any randomized parameters. Alternate the order of variant runs and/or swap the roles of machines if using multiple hosts.- Archive: dataset snapshot hash, DB config, OS image, benchmark script version, raw logs, explain outputs.- Automate with orchestration script that: resets caches, runs VACUUM/ANALYZE, primes cache (or clears it), runs warm-up, executes measurement, collects metrics, and stores results.- Repeat the full experiment at different times/days to detect variability; report noise and environmental anomalies.

7. Diagnostics & acceptance criteria- Compare p95/p99 and median, not only mean. If one variant reduces p95 by X% with no correctness regressions and acceptable resource usage, prefer it.- If improvements come at cost of more temp file usage or blocking, quantify trade-offs.- Recommend follow-ups: index tuning, rewrite suggestions, or incremental rollout with monitoring.

Example tools & commands- Postgres: EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON), pg_stat_activity, pgbench- Load: pgbench, JMeter, k6, custom async Python scripts- System metrics: vmstat/iostat/dstat, Prometheus + Grafana for visualization- Automation: Bash/Python scripts + Git for reproducibility

This harness produces reproducible, statistically-validated comparisons and rich diagnostics to make an evidence-based choice between the two aggregation variants.

EasyTechnical

92 practiced

Given table schema:

orders(
  order_id int primary key,
  customer_id int,
  amount numeric,
  created_at timestamp
)

Write a SQL query to compute the total amount for customer_id = 123 over the last 30 days and explain what index you would create so this query can be served with minimal I/O. Explain why your index helps and any trade-offs.

Sample Answer

To compute the total amount for customer_id = 123 in the last 30 days:

sql

SELECT
  customer_id,
  SUM(amount) AS total_amount
FROM orders
WHERE customer_id = 123
  AND created_at >= now() - interval '30 days'
GROUP BY customer_id;

Why this index helps:- Create a composite b-tree index on (customer_id, created_at, amount) or at minimum (customer_id, created_at): CREATE INDEX idx_orders_cust_created ON orders (customer_id, created_at);- The index lets the database efficiently seek to rows for customer_id = 123 and then filter by created_at range without scanning the whole table. The DB can perform an index range scan and either fetch amounts from the table (if index is non-covering) or get them directly from the index if amount is included (covering index).

Trade-offs:- Index maintenance cost: slows INSERT/UPDATE/DELETE and increases storage.- If you create a covering index including amount (e.g., (customer_id, created_at) INCLUDE (amount) on some DBs), queries avoid table lookups but index size grows.- If queries also filter by other columns or use different ranges, multiple indexes may be needed—balance read performance vs write cost and storage.

HardTechnical

81 practiced

Case study: You have a transactions table with 200M rows. A CFO report runs an aggregation over several columns and currently takes 8–12 minutes. As the data analyst responsible, present a step-by-step plan to diagnose and improve this report to under 30 seconds for interactive use. Include low-risk quick wins, medium-term schema/index changes, and long-term architectural considerations (columnar store, pre-aggregations).

Sample Answer

Step 1 — Clarify requirements & measure- Confirm the exact query(s), required columns, freshness SLA (<30s), concurrency, and acceptable staleness.- Capture current baseline: explain plan, runtime breakdown (CPU, IO, network), rows read, bytes scanned, and query plan with ANALYZE/EXPLAIN (show a few plan samples).

Quick wins (low risk, immediate)- Restrict scanned data: add WHERE time filters or push date range to the query; limit to necessary columns (avoid SELECT *).- Apply predicate pushdown and projection in the BI layer (Tableau/Power BI extracts or custom SQL).- Add appropriate query hints or session settings (increase work_mem / temp buffers) if DB-level tuning is possible.- Cache results at BI layer for short windows (e.g., 5–15 minutes) to avoid repeated recompute.

Medium-term (schema/indexing changes, 1–4 weeks)- Create covering indexes for the aggregation keys (e.g., CREATE INDEX ON transactions (date, dim1, dim2) INCLUDE(amt, count_col)). For large tables, use partial indexes on recent partitions.- Partition the table by date (monthly/daily) so queries scan fewer partitions.- Use clustering (REINDEX/CLUSTER) to physically order by common group-by keys to improve locality.- Convert wide text columns to smaller types; ensure numeric types are optimal.- Introduce materialized views for common heavy aggregations; schedule refresh frequency based on SLA (e.g., nightly + incremental refresh).

Long-term architecture (months)- Move analytical workloads to a columnar OLAP store (e.g., Redshift, BigQuery, Snowflake, ClickHouse) for massive scan reduction and vectorized execution.- Implement aggregate tables / rollups (hourly/daily/monthly) maintained by ETL or streaming (Debezium + kappa architecture) for interactive queries.- Adopt pre-aggregation frameworks (e.g., Cube.js, Druid, Pinot) for sub-second group-by lookups and high concurrency.- Introduce an intelligent caching layer (Redis or BI extracts) and autoscaling query engine.

Validation & monitoring- After each change, re-run explain and real workloads; track query latency P50/P95, bytes scanned, and cost.- Implement regression tests, alerting, and cost-monitoring to guard against query changes that regress performance.

Trade-offs & reasoning- Quick wins reduce scanned data and repeated compute with minimal risk.- Indexes/partitioning speed selective scans but add write cost and maintenance.- Columnar/pre-aggregations shift compute to ETL and storage cost but deliver consistent sub-30s interactivity at scale.

Example SQL (materialized view):

sql

CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT date_trunc('day', tx_date) AS day, dim1, SUM(amount) AS total_amount, COUNT(*) AS tx_count
FROM transactions
GROUP BY 1, dim1;
-- Refresh strategy: REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;

This staged approach yields immediate relief, medium-term durable improvements, and long-term architectural changes to reliably achieve <30s interactive reporting.

Unlock Full Question Bank

Get access to hundreds of Query Optimization and Execution Plans interview questions and detailed answers.

Join thousands of developers preparing for their dream job.