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

70 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?

MediumTechnical

91 practiced

You observe a slow join in a training data extraction job. The EXPLAIN shows a `Nested Loop` join where you expected a `Hash Join`. Describe three reasons the planner might choose a nested loop, and what changes (query rewrite, indexes, configuration) could encourage a hash join instead.

EasyTechnical

69 practiced

Explain predicate pushdown and why it's important when querying data stored in columnar formats (e.g., Parquet) or through foreign data wrappers. Give an example where pushing predicates to the storage layer reduces I/O and improves query performance for a features ETL job.

MediumTechnical

76 practiced

A large join produces duplicate rows due to a many-to-many relationship. This causes aggregation inflation when computing features. Using EXPLAIN and sample data, show how you'd detect that duplication is the source and propose query-level fixes to deduplicate safely without losing required data.

HardTechnical

91 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.

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.