Query Optimization Tasks

Introduction

Practical tuning tasks — given a slow query, make it fast. The interview gold: read the plan, identify the bottleneck, propose the fix, predict the new plan.

Understanding the topic

Core concepts to understand:

• Add the right index.
• Rewrite to be sargable.
• Replace correlated subquery with join.
• Use a CTE to materialize an expensive intermediate.
• Partition or pre-aggregate.

Syntax reference

Visual workflow / architecture:

Slow query → EXPLAIN → bottleneck node → fix → re-run → verify

Real-world use

Sr. backend interviews routinely hand you a slow query and ask you to make it fast. Confidence reading plans wins.

Best practices

• Read the plan first.
• Suggest one fix at a time and verify.
• Always quote before/after numbers.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Walk through optimizing a Seq Scan join.
• Q2. Replace correlated subquery — show how.
• Q3. When does an index NOT help?
• Q4. Cardinality estimation.
• Q5. Plan caching pitfalls.