An execution plan is the sequence of steps a database engine decides to take in order to answer a query, laid out as a tree of operations — scans, joins, sorts, filters — each one annotated with the engine’s own estimate of how expensive it will be. Running EXPLAIN in front of a query doesn’t run the query for real in most cases; it asks the database to show its work before committing to it. Reading that output correctly is the difference between guessing at why a query is slow and knowing, with specific evidence, exactly which operation is costing you time.
The best way to build that reading skill isn’t a glossary of node types. It’s watching one query go from slow to fast, plan by plan, and seeing what changed at each step.
The Query That Started This
Take a fairly ordinary reporting query: pull every order placed in the last 30 days for customers in a specific region, along with the customer’s name. Two tables — orders and customers — joined on customer ID, filtered on a date column and a region column.
SELECT c.customer_name, o.order_id, o.order_date, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
AND c.region = 'West';
On a table with a few hundred thousand orders, this query took over four seconds. Nothing about the SQL itself looks wrong — it’s a plain join with a plain filter. The only way to find out why it was slow was to ask the database to explain itself.
The First Plan: Reading It Top to Bottom, But Understanding It Bottom to Top
Running EXPLAIN ANALYZE on this query produced something like the following, trimmed for clarity:
Hash Join (cost=15234.00..48221.55 rows=812 width=64) (actual time=812.4..4102.9 rows=743 loops=1)
Hash Cond: (o.customer_id = c.customer_id)
-> Seq Scan on orders o (cost=0.00..31500.00 rows=98000 width=40) (actual time=0.02..3891.6 rows=97650 loops=1)
Filter: (order_date >= (CURRENT_DATE - '30 days'::interval))
Rows Removed by Filter: 302350
-> Hash (cost=9800.00..9800.00 rows=42000 width=32) (actual time=610.1..610.1 rows=41800 loops=1)
-> Seq Scan on customers c (cost=0.00..9800.00 rows=42000 width=32) (actual time=0.01..580.3 rows=41800 loops=1)
Filter: (region = 'West'::text)
Rows Removed by Filter: 8200
The layout is a tree, and the convention that trips up almost everyone at first is that the plan reads top to bottom on the page, but executes bottom to top. The innermost, most indented operations run first; their output feeds into the operation above them. Here, the two Seq Scans at the bottom run first, feeding rows into the Hash and then into the Hash Join at the top, which produces the final result.
Each line carries two very different kinds of numbers, and confusing them is one of the most common mistakes people make when reading a plan. The cost= figures are the planner’s own estimate, calculated before the query runs, based on statistics about table size and data distribution. The actual time= and actual rows= figures, which only appear because this was run with ANALYZE rather than plain EXPLAIN, are what really happened when the query executed. Plain EXPLAIN shows you only the estimate; EXPLAIN ANALYZE runs the query for real and shows you both side by side.
Spotting the Real Problem: The Seq Scan
The line that matters most here is Seq Scan on orders o, with Rows Removed by Filter: 302350. A sequential scan reads every single row in the table, in physical storage order, checking each one against the filter condition. On the orders table, that meant reading roughly 400,000 rows just to keep 97,650 of them — throwing away three out of every four rows it read, and paying the cost of reading all of them regardless.
That single scan accounted for roughly 3.9 of the query’s 4.1 total seconds. The join itself, the Hash step, and the customers scan barely register by comparison. This is the first real skill in reading a plan: don’t start at the top with the operation that has the biggest total cost number, since that number is often just the accumulated cost of everything beneath it. Look instead for the single operation with the largest jump in actual time relative to the rows it kept, because that’s usually where the money is being spent.
A sequential scan isn’t automatically bad — for small tables, or queries that need most of the rows anyway, it can be the cheapest available option, and the planner will choose it correctly. It becomes a problem specifically when a table is large and the filter is selective, meaning it throws away a large fraction of the rows it reads. That combination is exactly what this plan showed.
Why There Was No Index to Use
Checking the table definition explained the scan immediately: order_date had no index. Without one, the only way for the database to find rows within the last 30 days is to look at every row and check its date, which is precisely what the Seq Scan was doing. Adding an index gives the planner a second option — a structure it can search directly for the qualifying date range, rather than reading the whole table to find them.
CREATE INDEX idx_orders_order_date ON orders (order_date);
Creating this index doesn’t guarantee the planner will use it. The planner chooses between available strategies based on estimated cost, and for some queries — particularly ones expected to return a large fraction of the table — a sequential scan can still be cheaper than an index scan, because reading an index and then jumping around the table to fetch matching rows carries its own overhead. Whether the index gets picked up is something you confirm by running EXPLAIN again, not something you assume.
The Second Plan: What Changed
Re-running the same query after the index was in place produced a noticeably different plan:
Nested Loop (cost=0.43..3120.88 rows=812 width=64) (actual time=0.08..38.6 rows=743 loops=1)
-> Index Scan using idx_orders_order_date on orders o (cost=0.43..1890.20 rows=1520 width=40) (actual time=0.04..12.1 rows=1480 loops=1)
Index Cond: (order_date >= (CURRENT_DATE - '30 days'::interval))
-> Index Scan using customers_pkey on customers c (cost=0.29..0.81 rows=1 width=32) (actual time=0.01..0.01 rows=1 loops=1480)
Index Cond: (customer_id = o.customer_id)
Filter: (region = 'West'::text)
Two things changed, and both matter. First, the Seq Scan on orders became an Index Scan, going from 302,350 wasted row reads down to roughly 1,480 rows examined directly — the index let the database jump straight to the qualifying date range instead of checking every row in the table. Second, the join strategy itself changed, from a Hash Join to a Nested Loop.
That second change isn’t a coincidence, and understanding why it happened is where reading plans starts to feel less like memorizing terms and more like following a chain of cause and effect. A Hash Join builds an in-memory hash table from one side and probes it with the other — a strategy that pays off when both sides of the join are large. A Nested Loop, by contrast, takes each row from one side and looks up its match on the other side individually, which is cheap only when the number of outer rows is small and each lookup is fast. Once the index scan cut the orders side down to roughly 1,480 rows instead of 97,650, looping over that much smaller set and doing a fast indexed lookup into customers for each one became the cheaper overall strategy. The planner didn’t choose Nested Loop arbitrarily; it followed directly from the row count dropping earlier in the plan.
Total execution time can drop from just over four seconds to under 40 milliseconds — a hundred-fold improvement, achievable without touching the SQL itself. The query text is identical in both cases. Everything that changed happened at the plan level.
Checking Estimated Rows Against Actual Rows
One habit worth building into every plan review: compare the rows= estimate against the actual rows= figure on the same line. In the second plan, the orders index scan estimated 1,520 rows and returned 1,480 — close enough that the planner’s statistics were clearly trustworthy for this query. When those two numbers diverge by an order of magnitude or more, it’s usually a sign that the table’s statistics are stale, or that the query has a condition the planner can’t estimate accurately, such as a correlation between two columns it doesn’t track. A large gap here is often the first clue that a plan looks fine on paper but is quietly working from bad assumptions, and it’s worth investigating before trusting anything else the plan tells you.
Running ANALYZE tablename to refresh statistics is the first thing to try when estimates and actuals drift apart. It’s a cheap operation, and it’s frequently the entire fix when a previously fast query suddenly slows down after a large data load or bulk delete, since the planner is still working from a stale picture of the table.
Reading Cost Numbers Without Overinterpreting Them
The cost= figures deserve one caution: they’re not measured in seconds, dollars, or any real unit. They’re an internal, relative scale the planner uses to compare strategies against each other for the same query, based on configured assumptions about how expensive disk reads and CPU operations are relative to one another. A cost of 3120 in one plan and 3120 in a completely different query aren’t comparable to each other in any meaningful way. What matters is comparing the cost of one plan for a given query against an alternative plan for that same query — which is exactly what happens when you force a different strategy to test whether the planner’s default choice was actually the best one available, or when you compare a plan before and after adding an index, as in this case study.
What This Case Study Generalizes To
The steps that fixed this particular query aren’t specific to it. They form a repeatable routine for any slow query:
- Run EXPLAIN ANALYZE and locate the operation with the largest actual time relative to the rows it kept, rather than the operation with the largest total cost.
- Check whether that operation is a sequential scan on a large table with a selective filter — the most common single cause of avoidable slowness.
- Confirm there’s no usable index on the filtered or joined columns, and add one if there isn’t.
- Re-run EXPLAIN ANALYZE and confirm the scan type changed, rather than assuming the index will be used just because it exists.
- Compare estimated rows to actual rows at each step to catch stale statistics before they mislead any later decision.
A Quick Reference for the Node Types You’ll See Most
| Plan node | What it means | When it’s a warning sign |
|---|---|---|
| Seq Scan | Reads every row in the table | Large table + selective filter with no matching index |
| Index Scan | Uses an index to find matching rows directly | Rarely a problem on its own |
| Nested Loop | Loops over one side, looking up matches on the other | Expensive if the outer side turns out to be large |
| Hash Join | Builds a hash table from one side, probes with the other | Can spill to disk if the table doesn’t fit in memory |
| Sort | Orders rows explicitly, often for ORDER BY or a merge join | Costly on large row sets without a supporting index |
Execution plans stop looking like noise once you’ve traced one query through a before-and-after change and watched the numbers respond to a specific fix. The vocabulary — Seq Scan, Nested Loop, Hash Join — matters far less than the habit of asking, at each node, “how many rows came in, how many came out, and how long did that take relative to the rest of the plan.” That question is the same one whether the query has two tables or twelve.