Backend interview prep

Start

Execution Plans Interview Questions

Master SQL Execution Plans with interview-focused questions covering EXPLAIN, EXPLAIN ANALYZE, Table Scan, Index Scan, Index Seek, Nested Loop Join, Hash Join, Merge Join, Cardinality Estimation, Covering Indexes, Query Cost, and production troubleshooting.

Introduction

An Execution Plan is the roadmap that a database uses to execute a SQL query.

Instead of directly executing SQL, the database

  • Parses the SQL
  • Optimizes the query
  • Chooses the lowest-cost plan
  • Executes the plan

Understanding execution plans is one of the most important skills for

  • Backend Developers
  • Database Engineers
  • Performance Engineers
  • Solution Architects

Most production database performance issues can be solved by analyzing execution plans.

SQL Execution Architecture

flowchart LR

Application --> SqlQueryParserQuery["SQL Query --> Parser --> Query Optimizer --> Execution Plan --> Execution Engine --> Storage Engine --> Result"]

1. What is an Execution Plan?

Answer

An Execution Plan describes

  • How SQL will execute
  • Which indexes will be used
  • Join order
  • Join algorithms
  • Estimated cost
  • Rows processed

It is generated by the Query Optimizer.

2. Why are Execution Plans important?

Execution plans help identify

  • Missing Indexes
  • Table Scans
  • Expensive Joins
  • Poor Query Design
  • Slow Operations

3. How do you view an Execution Plan?

MySQL

EXPLAIN

SELECT *

FROM Employee

WHERE id=100;

PostgreSQL

EXPLAIN ANALYZE

SELECT *

FROM Employee;

Oracle

EXPLAIN PLAN FOR

SELECT *

FROM Employee;

SQL Server

Actual Execution Plan

Execution Plan Flow

flowchart LR

SQL --> OptimizerExecutionPlanExecutionengineexecution["Optimizer --> Execution Plan --> ExecutionEngine["Execution Engine"]"]

4. What is EXPLAIN?

EXPLAIN displays

  • Access Method
  • Join Order
  • Index Usage
  • Estimated Rows
  • Estimated Cost

without executing the query.

5. What is EXPLAIN ANALYZE?

EXPLAIN ANALYZE

executes the query

and displays

  • Actual Time
  • Actual Rows
  • Actual Cost
  • Actual Execution Plan

Very useful for production tuning.

6. Difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN EXPLAIN ANALYZE
Estimate Actual Execution
No Query Execution Executes Query
Faster More Accurate

7. What is Query Cost?

Query Cost is an estimate of resources required.

Includes

  • CPU
  • Memory
  • Disk IO
  • Network

Lower cost generally means a better plan.

8. What is Cardinality Estimation?

Cardinality means

the estimated number of rows

returned by each operation.

Accurate statistics

↓

Better execution plans.

Cardinality Estimation

flowchart LR

Statistics --> OptimizerEstimatedRowsExecutionplanexecution["Optimizer --> Estimated Rows --> ExecutionPlan["Execution Plan"]"]

9. What happens if statistics are outdated?

Optimizer may

  • Choose wrong index
  • Perform table scans
  • Use incorrect join order

Result

↓

Poor performance.

10. What is a Table Scan?

Database reads

every row

in the table.

Suitable only

for very small tables.

11. What is an Index Scan?

Database scans

the index

instead of the table.

May still read many index entries.

12. What is an Index Seek?

Database directly navigates

to matching index entries.

Fastest access method.

Table Scan vs Index Scan vs Index Seek

Access Method Performance
Table Scan Slow
Index Scan Medium
Index Seek Fast

Access Method

flowchart LR

Query --> IndexSeek

Query --> IndexScan

Query --> TableScan

13. What is a Covering Index?

A Covering Index contains

all columns

required by the query.

No table lookup required.

Example

INDEX

(last_name,

first_name,

salary)

14. Why are Covering Indexes faster?

No additional

table access

↓

Lower Disk IO

↓

Lower CPU

↓

Faster execution.

15. What is a Filter Operation?

Rows are filtered

after reading data.

Better optimization pushes filters

earlier

using indexes.

16. What is a Sort Operation?

Database sorts rows

using

ORDER BY

Large sorts consume

  • Memory
  • CPU
  • Temporary Storage

17. How can Sort operations be optimized?

  • Create indexes matching ORDER BY
  • Reduce returned rows
  • Avoid unnecessary sorting

18. What is a Nested Loop Join?

Outer table

↓

For every row

↓

Search inner table.

Best for

small result sets.

19. What is a Hash Join?

Creates a hash table

then probes matching rows.

Best for

large datasets.

20. What is a Merge Join?

Both inputs

must be sorted.

Excellent for

large sorted datasets.

Join Algorithms

flowchart LR

NestedLoop --> SmallTables

HashJoin --> LargeTables

MergeJoin --> SortedTables

21. What is Materialization?

Database stores

intermediate query results

temporarily

for reuse.

Common with

  • CTEs
  • Derived Tables
  • Subqueries

22. What is a Temporary Table?

Database creates

temporary storage

during query execution.

Usually indicates

additional work.

23. Why are Temporary Tables expensive?

Require

  • Memory
  • Disk IO
  • Additional CPU

Large temporary tables may spill to disk.

24. What is Predicate Pushdown?

Filters applied

as close as possible

to the storage engine.

Reduces rows processed.

25. What is Parallel Execution?

Database executes

multiple operations

simultaneously

using multiple CPU cores.

Supported by many enterprise databases.

26. Banking Example

Bad

Table Scan

↓

8 Million Rows

Better

Index Seek

↓

1 Row

Execution time

15 seconds

↓

8 milliseconds

27. E-Commerce Example

Query

WHERE

category='Laptop'

AND

brand='Dell'

Compound Index

↓

Index Seek

↓

Fast response.

28. HR Example

Search

Employee ID

↓

Primary Key Index

↓

Index Seek

29. Logging Example

Filter

Timestamp

↓

Partition

↓

Index

↓

Milliseconds.

30. Common Execution Plan Problems

  • Full Table Scan
  • Missing Index
  • Wrong Join Order
  • Large Sort
  • Temporary Table
  • Poor Cardinality
  • Outdated Statistics
  • Large Nested Loop

31. How do you improve Execution Plans?

  • Update statistics.
  • Create proper indexes.
  • Rewrite inefficient SQL.
  • Reduce returned rows.
  • Remove unnecessary joins.
  • Avoid SELECT *.
  • Optimize predicates.
  • Analyze actual execution plans.

Execution Plan Workflow

flowchart LR

SlowQuery["Slow Query"] --> ExplainAnalyzePlanIdentify["EXPLAIN --> Analyze Plan --> Identify Bottleneck --> Optimize SQL --> Validate --> Production"]

Enterprise Best Practices

  • Always analyze execution plans before production deployment.
  • Keep database statistics up to date.
  • Use EXPLAIN ANALYZE for actual performance validation.
  • Prefer Index Seeks over Table Scans.
  • Create covering indexes for frequently executed queries.
  • Minimize sorting and temporary tables.
  • Avoid unnecessary joins.
  • Monitor execution plans after schema changes.
  • Revalidate plans after large data growth.
  • Continuously monitor slow queries.

Quick Revision

Topic Key Point
Execution Plan Query Roadmap
EXPLAIN Estimated Plan
EXPLAIN ANALYZE Actual Runtime
Query Cost Estimated Resources
Cardinality Estimated Rows
Table Scan Reads Entire Table
Index Scan Reads Index
Index Seek Direct Index Lookup
Covering Index Index Only Access
Nested Loop Small Tables
Hash Join Large Tables
Merge Join Sorted Data
Materialization Intermediate Results
Temporary Table Additional Storage

Interview Tips

Interviewers frequently ask

  • What is an Execution Plan?
  • Difference between EXPLAIN and EXPLAIN ANALYZE.
  • Table Scan vs Index Scan vs Index Seek.
  • What is Cardinality Estimation?
  • What is a Covering Index?
  • Explain Nested Loop Join.
  • Explain Hash Join.
  • Why are Temporary Tables expensive?
  • How do you analyze a slow query?
  • Give a production execution plan example.

A strong production troubleshooting answer is:

  1. Capture the slow SQL.
  2. Generate the execution plan.
  3. Check access methods (seek vs scan).
  4. Verify join algorithms.
  5. Review estimated vs actual rows.
  6. Identify missing indexes or poor statistics.
  7. Optimize the SQL or indexes.
  8. Validate improvements using EXPLAIN ANALYZE.

This demonstrates practical production troubleshooting experience.

Summary

Execution Plans are the foundation of SQL performance optimization. They reveal how a database executes queries, including access methods, join algorithms, index usage, cardinality estimates, and execution costs. By understanding execution plans and using tools such as EXPLAIN and EXPLAIN ANALYZE, developers can identify bottlenecks and optimize queries effectively.

Mastering execution plans prepares Backend Developers, Database Engineers, Performance Engineers, and Solution Architects to diagnose and resolve real-world database performance issues in enterprise environments.