Behind the Scenes: Reading SQL Execution Plans Like a Pro

“Fast SQL doesn’t just come from good syntax — it comes from reading what the database is telling you.”

In professional SQL development, understanding how your queries run is just as important as writing them correctly. When you understand how the database executes your query, you can:

Fix performance bottlenecks
Choose the right indexes
Reduce query time from seconds to milliseconds

In this article, we’ll walk through real-world performance tuning using EXPLAIN, SHOW PLAN, cost-based optimization, and detailed breakdowns of Index Scan vs Index Seek.

Let’s explore it all with a real-life scenario: optimizing a reporting query on an Orders table.

Setup: A Reporting Query

Let’s say you have a report that fetches recent orders over a certain amount.

SELECT id, customer_id, total_amount, order_date
FROM Orders
WHERE order_date >= '2024-01-01' AND total_amount > 500;

It runs, but it’s slow. Your instinct? Add an index. But before that...

Step 1: Inspect with EXPLAIN or SHOW PLAN

Use database-specific explainers:

PostgreSQL / MySQL:

EXPLAIN SELECT id, customer_id, total_amount, order_date FROM Orders ...;

SQL Server:

SET SHOWPLAN_ALL ON;
GO
SELECT id, customer_id, total_amount, order_date FROM Orders ...;

✅ Output shows you:

Access path (Sequential Scan? Index Scan? Index Seek?)
Estimated rows
Cost metrics

Step 2: Add and Compare Indexing Options

Let’s add indexes and compare strategies:

-- Option 1: Composite index
CREATE INDEX idx_orders_date_total ON Orders(order_date, total_amount);

-- Option 2: Filtered index (SQL Server)
CREATE INDEX idx_orders_total_filtered
ON Orders(order_date)
WHERE total_amount > 500;

✅ Now run EXPLAIN ANALYZE or SHOWPLAN again and compare the new plan.

Index Seek vs Index Scan

Access Type	Description	Use When
Index Seek	Direct lookup using tree traversal	Ideal for precise matches
Index Scan	Scans range of index entries	Good for range filters
Table Scan	Scans entire table, ignoring indexes	Avoid unless absolutely needed

🧠 Tip: You want Index Seek for most queries — it’s fast, direct, and efficient.

Step 3: Use Real-World Cost-Based Optimization

Let’s compare execution plans before and after indexing:

Before

Seq Scan on Orders  (cost=0.00..500.00 rows=8000)

After Index

Index Seek using idx_orders_date_total (cost=0.30..50.00 rows=400)

✅ Performance improved 10x. Why?

Better cardinality estimation
Smaller scan range
Lower I/O

Bonus: Fixing a Scalar Subquery

Bad:

SELECT id,
  (SELECT COUNT(*) FROM OrderItems WHERE order_id = o.id) AS item_count
FROM Orders o;

✅ Replace with a join and aggregate:

SELECT o.id, COUNT(oi.id) AS item_count
FROM Orders o
LEFT JOIN OrderItems oi ON oi.order_id = o.id
GROUP BY o.id;

Scalar subqueries = one-per-row → 🔥 performance hits.

Key Concepts Covered

Feature	Purpose
`EXPLAIN`	Read the query planner output
Index types	Match access paths to filter logic
Cost fields	Understand which steps are expensive
Cardinality	Know how many rows the planner expects
Seek vs Scan	Optimize by controlling access strategy

Final Thoughts: Learn to Read the Signals

Execution plans are your best friends when diagnosing SQL performance. Like a mechanic reading engine diagnostics, you’ll:

Spot slowdowns before they hit prod
Avoid redundant indexes
Scale SQL with confidence

"Performance isn't luck — it's insight. And EXPLAIN is your flashlight."

#SQL #QueryOptimization #EXPLAIN #SHOWPLAN #Indexing #SeekVsScan #Performance

Cristian Sifuentes @cristiansifuentes