Sql Execution Plan

Last updated: Apr 3, 2026

how to optimize a SQL query ? how to do such via execution plan?

⸻

✅ What is an Execution Plan?

An execution plan (or query plan) is a breakdown of how the database engine executes a SQL query — including join order, index usage, table scan, and estimated cost.

You can get it via: • EXPLAIN (MySQL/PostgreSQL) • EXPLAIN ANALYZE (PostgreSQL – includes runtime) • EXPLAIN FORMAT=JSON (MySQL – detailed plan) • SQL Server: View actual plan in SSMS

⸻

🔍 Steps to Optimize via Execution Plan

  1. Identify Costly Operations • Look for: • Full Table Scans instead of Index Scans • Nested Loop Joins on large datasets • Sort operations or Hash Aggregates • Temp table / disk usage

  2. Check Index Usage • Ensure: • WHERE conditions and JOINs use indexed columns • Composite indexes are aligned with column order • Fix: • Add missing indexes • Remove unused/inefficient ones • Use INDEX HINTS if optimizer is suboptimal

  3. ** Avoid SELECT *** (wide rows) • Only retrieve the columns needed, to reduce I/O.

  4. Analyze Join Order • Optimizer chooses join order, but you can improve by: • Rewriting subqueries to JOINs • Using CTEs to isolate expensive branches • Pushing filters before joins

  5. Refactor WHERE/HAVING Clauses • Ensure predicates are: • Sargable (Search Argument-able): avoid WHERE YEAR(date_col) = 2023 • Use explicit joins instead of correlated subqueries

  6. Check for Over-Aggressive GROUP BY / ORDER BY • Index columns used in GROUP BY, ORDER BY • Add covering indexes if repeated often

⸻

🛠 Example (MySQL)

EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

Look for: • type should be ref or index • key should not be NULL • rows should be low

If type=ALL, and key=NULL ⇒ full table scan → Add index on customer_id.

⸻

📌 Summary

Optimization Focus Via Execution Plan Index usage Check key, rows, type Join efficiency Avoid nested loops on large data Table scans Look for type=ALL Sort/aggregation cost Watch for filesort / temporary table Filter effectiveness Ensure filters are early in plan

⸻

Perfect — let’s walk through a realistic SQL optimization scenario using an EXPLAIN plan. I’ll cover:

⸻

🔍 Scenario: You have this query:

SELECT o.id, o.order_date, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = ‘shipped’ AND c.region = ‘US’ ORDER BY o.order_date DESC LIMIT 100;

⸻

🔎 Step 1: Run EXPLAIN (MySQL / PostgreSQL syntax similar)

EXPLAIN SELECT o.id, o.order_date, c.name FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = ‘shipped’ AND c.region = ‘US’ ORDER BY o.order_date DESC LIMIT 100;

⸻

📋 Step 2: Understand the Key Metrics in Execution Plan

Metric What It Means Ideal Value / Issue to Watch For type Access method (ALL, index, ref, eq_ref) ✅ Prefer ref or eq_ref over ALL (full scan) key Index being used ❗ If NULL, no index is used rows Estimated rows scanned ✅ Lower is better filtered Percentage of rows passed to next stage ❗ Low = bad filter effectiveness Extra Additional info (Using temporary, filesort) ❗ “Using temporary” or “filesort” = slow

⸻

🧪 Sample EXPLAIN Output (MySQL-like):

id table type key rows Extra 1 customers ALL NULL 100000 Using where; Using join buffer 1 orders ref idx_customer_id 1000 Using where; Using filesort

⸻

🚨 Problems Found 1. customers is doing a full table scan • key = NULL, type = ALL → no index on region 2. orders is using filesort • ORDER BY o.order_date not covered by index

⸻

✅ How to Improve the SQL

🔧 Step 1: Add Missing Indexes

– Add index to speed up customer filter CREATE INDEX idx_customers_region ON customers(region);

– Add compound index for better sort and filter CREATE INDEX idx_orders_status_date ON orders(status, order_date DESC);

💡 Optional: Use Covering Index if needed

If the query often runs with just a few columns:

CREATE INDEX idx_orders_covering ON orders(status, order_date DESC, customer_id, id);

⸻

💬 After Optimization: Re-run EXPLAIN

Expected improvements:

Metric Expected Fix type ref or range instead of ALL key Non-NULL, showing your new index rows Reduced significantly Extra No filesort, temporary, or join buffer

⸻

✅ General Tips

Symptom Fix ALL in type Add index to WHERE / JOIN columns filesort in Extra Add index matching ORDER BY Using temporary in Extra Rewrite query or add index High rows count Rewrite logic or split into subqueries Poor filter % (filtered < 30%) Improve WHERE clause or pre-filter in app logic

⸻

Great! Let’s dive deeper by showing how to use EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=JSON (MySQL) for an even more insightful SQL performance diagnosis.

⸻

Example in PostgreSQL

Suppose this query is slow:

SELECT * FROM orders WHERE status = ‘shipped’ ORDER BY created_at DESC LIMIT 100;

You run:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = ‘shipped’ ORDER BY created_at DESC LIMIT 100;

You’ll get output like:

Limit (cost=0.43…12.59 rows=100 width=124) -> Index Scan using idx_orders_status_created on orders (cost=0.43…4320.85 rows=34345 width=124) Index Cond: (status = ‘shipped’)

✅ Good Signs: • Uses Index Scan instead of Seq Scan • Uses Index Cond, meaning index is applied • Reasonable cost and row estimate

⸻

🔍 What You Should Look for in EXPLAIN ANALYZE

🔎 Clue 📌 What to Check / Improve Seq Scan Create index on the filter column(s) Sort Method: external Increase work_mem or index on ORDER BY column Rows Removed by Filter Indicates filtering happening after scan — try moving to index Loops > 1 Often means nested loop — check for better join strategies High Actual Time Look at step-by-step time, see if sort/join steps dominate Rows Planned ≠ Actual Bad estimate — consider running ANALYZE or tuning planner stats

⸻

🚀 Quick Fix Checklist for SQL Optimization

Problem Fix Slow join Index the joined columns (JOIN ON …) Slow ORDER BY Add index on sorted column, especially with LIMIT Low selectivity (filtered = low) Avoid OR, use UNION, or refactor query Big data reads Consider query partitioning, caching, or materialized views High CPU due to sort/temp files Add LIMIT, rewrite query, or adjust Postgres configs

⸻

Bonus: Tools to Visualize & Explain • PostgreSQL: EXPLAIN (ANALYZE, BUFFERS, VERBOSE) Or use https://explain.dalibo.com/ • MySQL: Use EXPLAIN FORMAT=JSON for more structure and detail.

⸻

Would you like to share your own EXPLAIN output for analysis or want a custom query optimized for your schema?

← Back to FAQs Edit on GitHub →