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.

EasyTechnical

0 practiced

What conditions must be met for an index-only scan to be used by the planner? Include implications for visibility maps/vacuuming, required columns, and how covering indexes reduce I/O.

HardSystem Design

0 practiced

Design a safe production rollout plan for creating a large index on a high-traffic table without blocking writes. Include steps such as concurrently building the index, monitoring write/replication metrics, staged rollout, fallback, and deciding when to drop the index if it hurts performance.

HardTechnical

0 practiced

A query joins many small dimension tables to a huge fact table and uses nested-loop join with index lookups, but latency is still too high. Explain the conditions under which converting to a hash join or using a chained set of pre-aggregations would improve latency. Provide examples and discuss memory trade-offs.

EasyTechnical

1 practiced

Define selectivity. Explain with an example why a highly selective predicate is more likely to use an index and how combined predicate selectivity should affect join order choices in the optimizer.

MediumTechnical

0 practiced

Explain how IN (...) with a large literal list can result in different plans than EXISTS, and how list size, nulls, and value distribution influence performance. Provide guidance on when to rewrite IN to JOIN or EXISTS for better performance.

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.