Reading SQL Execution Plans: A Beginner's Guide (Why Your Query is Slow)

You wrote a query. It works. But it takes 15 seconds to run. Why? To fix it, you need to stop looking at the code and start looking at the Execution Plan.

SQL is a declarative language. You tell the database what you want ("Give me all users named John"), but you don't tell it how to get it. The database has a brain called the Query Optimizer that decides the best path to find that data.

Sometimes, the Optimizer makes bad choices. Or, more likely, you haven't given it the right tools (indexes) to do its job. The Execution Plan is the map of the path the database took.

How to see it: In almost every SQL database (PostgreSQL, MySQL, SQLite), you simply put the word EXPLAIN before your query.

EXPLAIN ANALYZE SELECT * FROM users WHERE last_name = 'Smith';

Concept 1: The Dreaded "Table Scan" (Seq Scan)

Imagine you are in a library looking for a book titled "Harry Potter." But the library has no sorting system. The books are random.

To find the book, you have to walk down every aisle, pick up every single book, and check the cover. If there are 1 million books, this takes forever.

In SQL, this is a Table Scan (or Sequential Scan). It means the database had to read every single row in the table to find your data because it didn't know where to look.

If you see "Seq Scan" on a large table, your query is slow.

Concept 2: The "Index Seek" (The Fast Path)

Now imagine the library has a digital catalog. You type "Harry Potter," and it tells you: "Aisle 4, Shelf 2." You walk straight there and grab it.

In SQL, this is an Index Seek. An index is a sorted copy of specific columns (like a phone book). The database jumps directly to the "S" section for "Smith" and ignores the rest.

If you see "Index Seek" or "Index Scan," your query is optimized.

How to Fix a Slow Query

Let's look at a real example.

The Slow Query:

SELECT * FROM orders WHERE customer_id = 500; -- Plan: Seq Scan on orders (Cost: 1000.00)

The database is scanning the whole orders table because it doesn't have an index on `customer_id`.

The Fix:

CREATE INDEX idx_orders_customer ON orders(customer_id);

The Fast Query:

SELECT * FROM orders WHERE customer_id = 500; -- Plan: Index Scan using idx_orders_customer (Cost: 5.00)

The cost dropped from 1000 to 5. The query is now 200x faster.

Common Red Flags in Plans

When reading an execution plan, look for these keywords depending on your database (Postgres/SQL Server):

Operation	Meaning	Good or Bad?
Seq Scan / Table Scan	Reading every row.	BAD (unless table is tiny)
Index Seek	Jumping to specific row.	GOOD
Key Lookup	Found ID in index, but had to go back to table for other columns.	OK (but can be improved)
Sort	Manually sorting data in memory.	Expensive

Conclusion

You cannot optimize what you cannot measure. Before you blame the server or the network for a slow app, run EXPLAIN. If you see a Table Scan on a million rows, you know exactly what to do.

Download January Skills: SQL Optimization Cheat Sheet