How to Read SQL Execution Plans: A Beginner’s Guide

You've written a SQL query. It returns the right results. But it takes forever to run.

You assume you need an index, or maybe you need to rewrite the join. But without looking under the hood, you're just guessing.

To truly optimize a query, you need to understand how the database executes it. That's where the Execution Plan comes in. It's the roadmap the database engine generates to fetch your data, and reading it is a superpower every developer needs.

What is an Execution Plan?

When you submit a SQL query, the database doesn't just run it immediately. First, the Query Optimizer analyzes your SQL and determines the most efficient way to execute it. It considers:

• Which tables to query first?
• Should it use an index or scan the whole table?
• How should it join the tables (Nested Loop, Hash Join, Merge Join)?

The result of this analysis is the Execution Plan.

Using EXPLAIN

In most SQL databases (PostgreSQL, MySQL, SQLite), you see the plan by prepending EXPLAIN to your query.

EXPLAIN SELECT * FROM users WHERE id = 100;

For even more detail, like actual run times, use EXPLAIN ANALYZE (in Postgres) or EXPLAIN QUERY PLAN (in SQLite).

Key Concepts: Scans vs. Seeks

The most fundamental thing to look for is how the database finds rows.

1. Full Table Scan (The Slow One)

The database reads every single row in the table to find matches. This is fine for tiny tables but disastrous for large ones.

Look for keywords like: SCAN TABLE, Seq Scan, Full Table Scan.

2. Index Scan / Seek (The Fast One)

The database uses an index to jump directly to the rows you need, like using the index at the back of a book.

Look for keywords like: SEARCH TABLE, Index Scan, Index Seek.

Interactive Demo: Scan vs. Index

Let's see the difference in action. We'll create a table with 10,000 rows. First without an index, then with one.

What to watch for:

1. In the first output, look for SCAN TABLE. That's bad for large datasets.
2. In the second output, look for SEARCH TABLE ... USING INDEX. That's much better!

Understanding Join Methods

When you join tables, the optimizer has to decide how to match rows.

Nested Loop Join

Great for small datasets or when one side is very small. It iterates through the first table and looks up matches in the second table one by one. Analogy: For every person in Room A, go ask everyone in Room B if they are friends.

Hash Join

Better for larger datasets. It builds a hash table (lookup map) in memory for one table and then scans the other to find matches. Analogy: Make a list of everyone in Room A. Then let everyone in Room B walk by and check the list.

Best Practices for Query Tuning

Based on execution plans, here is how you fix slow queries:

1. Eliminate Full Table Scans: If you see a scan on a large table in a WHERE or JOIN clause, consider adding an index on that column.
2. Watch for High Costs: Plans usually show a "cost" number. While relative, highly skewed costs indicate the bottleneck.
3. Be Careful with Functions: WHERE YEAR(date_col) = 2023 often prevents index usage (making it "non-sargable"). Use WHERE date_col >= '2023-01-01' AND date_col < '2024-01-01' instead.
4. Select Only What You Need: SELECT * can prevent "Index Only Scans" (where the DB finds data solely in the index without touching the main table).

Conclusion

Reading execution plans may look like deciphering ancient hieroglyphs at first, but it's the most reliable way to solve existing performance problems. Next time your query hangs, don't guess—EXPLAIN it!