Behind the Scenes: Reading SQL Execution Plans Like a Pro

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

May 12, 2025 - 19:50

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