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

0 practiced

A query plan shows a large `Sort` operator consuming significant time. The sort is required for `ORDER BY created_at DESC LIMIT 100`. Discuss possible approaches to avoid the large in-memory or external sort: indexes, materialized views, maintaining pre-sorted data, or using cursors. Which would you choose for a feature retrieval API with low latency SLA and why?

HardTechnical

0 practiced

You see a query repeatedly doing random I/O due to poor locality from the primary key layout (e.g., UUIDs). For a timeline of user events you want sequential write and efficient range scans. Discuss physical layout strategies (sequential keys, CLUSTER, BRIN indexes) and their trade-offs for write throughput and query speed.

EasyTechnical

0 practiced

Explain the concept of selectivity and how histograms and most-common-values in DB statistics help the optimizer. As data distributions change over time (e.g., seasonality in user activity), how should you adjust statistic collection and what alerts would you set to detect when statistics are out-of-date?

EasyTechnical

0 practiced

A query heavily filters using a boolean flag that is mostly true (e.g., 99% true). Explain why an index on that boolean column might be useless and propose index or schema alternatives (partial index, multicolumn index, or re-encoding) to improve selectivity for queries that typically filter for the rare false case.

MediumTechnical

0 practiced

A team plans to join a 10M-row users table to a 100M-row events table for building features. Describe how join order affects cost and when you would manually reorder joins vs relying on the optimizer. Include considerations for cross-joins and highly selective filters.

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.