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

Write a SQL query in PostgreSQL that flags outlier transactions where amount > mean + 3 * stddev per user over the past 365 days. Given a transactions table (transaction_id PK, user_id, amount numeric, occurred_at timestamp), show a query using window functions and explain any assumptions about nulls and small sample sizes.

HardSystem Design

0 practiced

Design an index strategy for a high-throughput feature-store table with writes at 10k TPS and reads for online lookup by feature_key and user_id. Consider composite indexes, partial indexes, and the impact on write latency and index bloat. Explain your reasoning and trade-offs.

HardTechnical

0 practiced

You are tuning PostgreSQL settings for a data warehouse used for offline feature computation. Explain the effect of `work_mem`, `maintenance_work_mem`, `effective_cache_size`, and `shared_buffers` on query planning and execution. Which settings impact the choice between hash join and merge join?

HardTechnical

0 practiced

Write a SQL query (PostgreSQL) that uses EXPLAIN ANALYZE to compare two approaches for joining customers and orders: (A) JOIN with an index on orders(customer_id), (B) using EXISTS subquery. Include how you capture and compare timing, buffer usage, and explain plan details programmatically for automated benchmarking.

MediumTechnical

0 practiced

A training job's extraction query involves joining to a slowly-updating dictionary table with low cardinality. Would you recommend a `JOIN`, `IN` subquery, or loading the dictionary into application memory and filtering client-side? Discuss latency, consistency, and caching trade-offs for the ML pipeline.

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.