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

83 practiced

You have a multi-tenant feature table partitioned by tenant_id and date. A daily aggregation query filters by tenant_id but not date. EXPLAIN shows many partitions being scanned. Describe how partition pruning works and propose changes (query rewrite or partitioning strategy) to reduce scanned partitions for tenant-specific queries.

HardTechnical

92 practiced

You're given two EXPLAIN plans (in JSON format) for the same query before and after a schema change. Describe how you'd programmatically compare the plans to detect regressions: which keys/metrics you'd compare, thresholds to alert on, and how you'd handle noise across runs.

HardTechnical

95 practiced

You're responsible for feature retrieval in low-latency online inference. A lookup query performs poorly due to high cardinality filters on a JSONB column. Describe indexing strategies (GIN, expression indexes) and query rewrites to speed JSONB lookups in PostgreSQL for predictable key lookups.

MediumTechnical

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

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.

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.