After this post, you’ll be able to run EXPLAIN on a slow query, locate the single operation responsible for most of its cost, and explain in plain language why the database chose that path instead of a faster one. That’s the practical target. Everything below is organized to get you from “I’ve never opened a query plan” to “I can diagnose a performance problem from one” in stages, comparing what a beginner needs against what an advanced reader is already doing differently.
An execution plan is the database’s own explanation of how it intends to (or did) retrieve your data — which indexes it touched, which order it joined tables in, and how many rows it expected at each step along the way. Most people never look at one until a query that used to run in milliseconds suddenly takes twenty seconds, and at that point the plan stops being optional reading.
Beginner: What an Execution Plan Actually Shows You
At the beginner level, the goal isn’t mastery — it’s just being able to produce a plan and recognize its basic shape without panicking at the output.
Running EXPLAIN in front of any query (the exact keyword and syntax varies slightly by database — EXPLAIN in PostgreSQL and MySQL, SET SHOWPLAN or the graphical plan in SQL Server) returns a tree of operations rather than your actual query results. Each operation in that tree represents one step the database takes: reading a table, scanning an index, joining two row sets, sorting a result, or filtering rows that don’t match your WHERE clause.
Reading the tree starts from the innermost or bottom-most operation and works outward or upward — that’s typically where the database actually starts, reading raw rows before anything gets filtered, joined, or sorted on top of them. A beginner’s first goal is simply matching each line in the plan back to a piece of the SQL that produced it: this scan corresponds to that table, this join corresponds to that ON clause.
Two words show up constantly and deserve attention before anything else: scan and seek (or their equivalents across database systems). A scan means the database is reading through rows more broadly than you’d probably like, often the entire table, checking each one against your filter. A seek — or an index seek — means the database jumped almost directly to the rows it needed using an index, without touching everything else. Spotting a full table scan on a large table is usually the first real insight a beginner takes away from a plan, and it’s often the reason the query felt slow in the first place.
Beginner: Estimated vs Actual — A Distinction Worth Learning Early
Most databases offer two flavors of plan: an estimated plan, generated without running the query, and an actual (or executed) plan, generated by running the query and recording what really happened at each step.
The estimated plan relies on statistics the database keeps about your tables — roughly how many rows exist, roughly how values are distributed across a column — to guess how many rows each operation will produce. The actual plan replaces those guesses with real row counts, since the query actually ran.
For a beginner, the estimated plan is enough to get oriented: it shows the shape of the plan and roughly where the cost sits without waiting for a slow query to finish. The moment a query starts behaving unexpectedly, though, comparing estimated rows against actual rows at each step becomes far more useful, and that comparison belongs solidly in the next section.
Advanced: Reading Cost, Not Just Structure
An advanced reader isn’t just identifying which operations ran — they’re comparing the relative cost the database assigns to each one, and using that number to decide where their tuning effort should go.
Most plans attach a cost figure to each operation, often expressed as a percentage of the total query cost. A join step showing 85% of total cost, sitting next to five other steps splitting the remaining 15%, tells you immediately where to focus: tuning anything besides that join is a rounding error next to fixing the join itself. Beginners tend to scan a plan looking for anything unfamiliar; advanced readers scan a plan looking specifically for the largest number.
This is also where the estimated-versus-actual comparison from the beginner section turns into a genuine diagnostic tool. If the optimizer estimated 200 rows coming out of a join but 400,000 rows actually came out, that gap is usually the root cause of a bad plan — the database chose a join strategy suited to 200 rows, and that strategy falls apart badly at 400,000. Stale statistics are the most common reason for this kind of mismatch, and refreshing them (updating statistics, or running the equivalent maintenance command for your database) is often a quicker fix than rewriting the query itself.
Beginner: Recognizing a Missing Index
One pattern is worth learning before almost any other, because it accounts for a large share of the slow queries a beginner will encounter: a full table scan feeding into a filter on a column with no index.
If the plan shows a scan reading every row in a table, followed immediately by a filter operation discarding most of those rows based on a WHERE condition, that’s usually a strong signal an index on the filtered column would let the database seek directly to the matching rows instead of reading everything first. Some database tools will even suggest this index directly inside the plan output, which is a reasonable starting point, though not something to accept without checking it against how the column is actually queried elsewhere.
A beginner doesn’t need to understand index internals to act on this pattern. Recognizing “big scan, then a filter that throws most of it away” as a shape worth investigating is enough to catch a large fraction of everyday performance problems.
Advanced: Join Order and Join Strategy
Once basic scans and seeks are second nature, an advanced reader starts paying attention to two things a beginner usually skips past: the order tables are joined in, and which join algorithm the database chose for each pairing.
Databases typically choose between a few join strategies — nested loop, hash join, and merge join being the most common labels across systems — and each one suits a different situation. A nested loop join tends to work well when one side of the join is small; it gets expensive fast when both sides are large, since it’s effectively looping through one set of rows once for every row in the other. A hash join usually handles large, unsorted row sets better, building an in-memory structure to match rows more efficiently than looping ever could.
Join order matters just as much as join strategy. The database decides which table to start reading from and which to join in second, third, and so on, based on its row estimates — and a bad row estimate early in that chain can push it toward starting with the wrong table, inflating cost at every subsequent step. An advanced reader looking at a slow plan will often trace the join order first, checking whether the database’s assumptions about table size at each step line up with reality, before assuming the query itself is written badly.
Beginner vs Advanced: A Side-by-Side Summary
| Situation | Beginner approach | Advanced approach |
|---|---|---|
| Opening a plan | Match each line to a piece of SQL | Identify the operation with the highest relative cost |
| Seeing a scan | Notice it might be slow | Check whether an index would convert it to a seek, and whether that index already exists elsewhere unused |
| Estimated vs actual rows | Aware the two numbers can differ | Uses the gap between them to diagnose stale statistics or bad estimates |
| Joins | Confirms which join corresponds to which ON clause | Evaluates join order and join algorithm against actual table sizes |
| Fixing a problem | Adds an index where a scan-then-filter pattern appears | Considers rewriting the query, updating statistics, or restructuring the join before touching indexes |
Advanced: When the Fix Isn’t an Index
Not every slow plan gets solved by adding an index, and this is where beginner and advanced instincts diverge most sharply. A beginner sees a scan and reaches for an index. An advanced reader first checks whether an index would even help — a query that touches most of a table’s rows anyway often runs just as well, or better, with a full scan, since an index seek that ends up fetching most of the table can cost more than reading it sequentially in the first place.
Sometimes the real fix is restructuring the query itself: rewriting a correlated subquery as a join, removing a function wrapped around an indexed column that quietly disables index use, or splitting one enormous query into smaller pieces the optimizer can plan more accurately. An execution plan won’t hand you these rewrites directly — it tells you where the cost sits, not necessarily what to change syntactically. That interpretive step is what separates someone who can run EXPLAIN from someone who can act on what it shows.
Putting Both Levels Together
Reading an execution plan well doesn’t require abandoning the beginner steps once you’ve reached the advanced ones — it requires layering them. Start by matching operations to SQL and spotting obvious scans, the way a beginner would. Then move to cost figures, estimated-versus-actual gaps, and join strategy, the way an advanced reader would, using the beginner-level map as the scaffolding for that deeper pass.
Most real diagnostic work moves back and forth between these two levels rather than sitting cleanly in one or the other: notice a big scan (beginner), check its cost percentage (advanced), consider an index (beginner), then confirm the row estimates driving the optimizer’s decision aren’t wildly off before committing to that fix (advanced). Query plans reward that kind of iterative reading far more than a single top-to-bottom pass ever will.
Paste your slow query’s execution plan (or describe what operations it contains) and tell me what runtime you’re seeing — I can help you figure out which operation is worth fixing first and what that fix should look like.