How To Read SQL EXPLAIN Output

Every developer eventually meets the query that was fast in development and crawls in production. The code looks fine. The table has an index, or you think it does. Yet the page takes seconds to load and nobody can say why. The honest answer is usually that the database is doing something different from what you assumed, and the only way to see that is to ask it.

A SQL query describes what data you want, not how to fetch it. The database has to decide how: which indexes to use, in what order to join tables, whether to sort in memory or on disk. That decision is called the execution plan, and a query that looks innocent can produce a very expensive plan when the table grows, the data is skewed, or the statistics are stale.

Learning to read an execution plan is one of the highest-leverage database optimization skills you can build. It turns “this query feels slow” into “this query scans 200,000 rows to return 18, then sorts them.” Once you can say that sentence, the fix is usually obvious. In this guide, I will walk you through PostgreSQL and MySQL output, explain each common operation, show a before and after example, and connect it all to Rails and Active Record.

What Is SQL EXPLAIN?

EXPLAIN is a statement that asks the database to show you its execution plan for a query instead of (or in addition to) returning results. The plan is a tree of operations. Each operation reads rows, filters them, joins them, sorts them, or aggregates them, and passes the results up to its parent.

Databases provide EXPLAIN because the query planner (also called the optimizer) makes cost-based decisions that are invisible from the outside. The planner uses table statistics, such as row counts and value distributions, to estimate the cost of several candidate strategies and picks the one it believes is cheapest. EXPLAIN lets you inspect that choice.

A few things are worth knowing before we go further:

• EXPLAIN is not part of the SQL standard. Both PostgreSQL and MySQL implement it, but the syntax, output format, and operation names differ.
• Plans depend on context. The same query can produce different plans depending on the database engine, version, schema, indexes, statistics, data distribution, and parameter values. A plan from your laptop with 1,000 rows may look nothing like the plan in production with 50 million.
• A plan is a hypothesis, not a verdict. It tells you what the planner intends to do and why it thinks that is cheap. You still have to verify that it is actually fast.

EXPLAIN vs. EXPLAIN ANALYZE

This is the single most important distinction in this article.

Plain EXPLAIN shows the estimated plan. The database plans the query but does not run it. Every number you see (cost, rows) is a prediction based on statistics. It is safe, fast, and works on queries that would take hours to run.

EXPLAIN ANALYZE shows the actual plan. The database plans the query, executes it, and reports real measurements alongside the estimates: actual row counts, actual time, and loop counts. This is what lets you compare what the planner expected with what really happened.

Be careful with data-modifying statements

Because EXPLAIN ANALYZE truly runs the statement, running it on an UPDATE, DELETE, or INSERT in PostgreSQL changes your data. This surprises many developers the first time.

If you need real measurements for a data-modifying statement, wrap it in a transaction and roll it back:

BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'archived'
WHERE created_at < now() - interval '2 years';

ROLLBACK;

Some precautions to keep in mind:

• Do this only where it is safe and suitable. The statement still takes real locks and does real work while it runs, so avoid doing it casually on a busy production primary.
• Triggers fire during the run. A rollback undoes row changes, but it cannot undo external side effects, such as a function that calls out to another system. Sequence values consumed by the statement are also not rolled back.
• Running a long write inside a transaction holds locks longer than you might expect. If transactions are new to you, the practical guide to Rails transactions explains how they behave.

If you only need the estimated plan, plain EXPLAIN on a data-modifying statement is always safe because it does not execute anything.

How to Run EXPLAIN

PostgreSQL

The basic form is simply the keyword in front of your query:

EXPLAIN
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 4821
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 10;

For real diagnostic work, you will mostly use the parenthesized options form:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 4821
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 10;

• ANALYZE executes the query and adds actual timings and row counts.
• BUFFERS reports how many memory pages (buffers) each node read from cache or from disk. Older versions need you to request it explicitly, and newer versions may include it with ANALYZE by default.
• VERBOSE adds details such as output columns.
• FORMAT JSON (also YAML and XML) produces machine-readable output that many visualizers can load.

If your query uses parameter placeholders like $1 (common in prepared statements), recent PostgreSQL versions offer EXPLAIN (GENERIC_PLAN) to see a plan without supplying values. Check your version’s documentation for availability.

MySQL

MySQL’s classic EXPLAIN returns a table with one row per table or subquery access:

EXPLAIN
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 4821
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 10;

A representative result for a table with no useful index looks like this:

+----+-------------+--------+------+---------------+------+---------+------+--------+----------+-----------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                       |
+----+-------------+--------+------+---------------+------+---------+------+--------+----------+-----------------------------+
|  1 | SIMPLE      | orders | ALL  | NULL          | NULL | NULL    | NULL | 198000 |     1.00 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+--------+----------+-----------------------------+

MySQL also offers other formats:

EXPLAIN FORMAT=TREE SELECT ...;   -- tree output, closer to PostgreSQL's style
EXPLAIN FORMAT=JSON SELECT ...;   -- detailed JSON output
EXPLAIN ANALYZE SELECT ...;       -- executes the query, reports actual timings (MySQL 8.0.18+)

Key syntax differences

Always check the documentation for your exact version, because both databases keep adding options and changing details.

Understanding an Execution Plan

Let’s read a realistic PostgreSQL plan. Imagine an e-commerce app with an orders table (200,000 rows) and a customers table (20,000 rows), and this query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, o.id, o.total_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 20;

Here is sample output. The numbers are illustrative values chosen to show the format, not benchmarks. Your output will differ.

Limit  (cost=5940.00..5940.05 rows=20 width=42) (actual time=48.010..48.014 rows=20 loops=1)
  Buffers: shared hit=1300 read=1400
  ->  Sort  (cost=5940.00..5965.00 rows=10000 width=42) (actual time=48.008..48.010 rows=20 loops=1)
        Sort Key: o.created_at DESC
        Sort Method: top-N heapsort  Memory: 28kB
        Buffers: shared hit=1300 read=1400
        ->  Hash Join  (cost=650.00..5750.00 rows=10000 width=42) (actual time=9.500..46.000 rows=9876 loops=1)
              Hash Cond: (o.customer_id = c.id)
              Buffers: shared hit=1300 read=1400
              ->  Seq Scan on orders o  (cost=0.00..5000.00 rows=10000 width=28) (actual time=0.020..38.100 rows=9876 loops=1)
                    Filter: (status = 'pending'::text)
                    Rows Removed by Filter: 190124
                    Buffers: shared hit=1200 read=1300
              ->  Hash  (cost=400.00..400.00 rows=20000 width=22) (actual time=6.000..6.000 rows=20000 loops=1)
                    Buckets: 32768  Batches: 1  Memory Usage: 1200kB
                    ->  Seq Scan on customers c  (cost=0.00..400.00 rows=20000 width=22) (actual time=0.010..3.000 rows=20000 loops=1)
Planning Time: 0.250 ms
Execution Time: 48.500 ms

How to read the structure

Plans are trees, and you read them from the innermost, most-indented node outward. Data flows upward. The arrows (->) mark child nodes. Here is what happens, in execution order:

1. Seq Scan on customers reads every customer row.
2. Hash builds an in-memory hash table from those rows, keyed by id.
3. Seq Scan on orders reads every order row and keeps only those where status = 'pending'.
4. Hash Join probes the hash table for each surviving order to find its customer.
5. Sort orders the joined rows by created_at DESC.
6. Limit returns the first 20.

What the numbers on each line mean

Each node has two parenthesized groups:

• (cost=5000.00 rows=10000 width=28) is the estimate from the planner. cost is shown as startup..total, rows is the estimated row count, and width is the estimated average row size in bytes.
• (actual time=0.020..38.100 rows=9876 loops=1) is the measurement, present only with ANALYZE. The time is in milliseconds, shown as first row..all rows.

Other lines add context:

• Filter and Rows Removed by Filter tell you the node read 200,000 rows but threw away 190,124 of them. That is a useful clue: lots of work for few results.
• Buffers shows shared hit (pages found in PostgreSQL’s cache) and read (pages fetched from outside it, either disk or the operating system cache).
• Sort Method: top-N heapsort means the sort only needed to track the top 20 rows because of LIMIT, so it used 28 kB of memory instead of sorting everything.
• Planning Time and Execution Time separate how long the planner took from how long the query ran.

Watch out for loops

When a node has loops greater than 1, its actual time and rows values are per-loop averages. You have to multiply to get the totals. This commonly appears on the inner side of a nested loop:

Nested Loop  (cost=0.29..1250.00 rows=500 width=40) (actual time=0.030..4.100 rows=500 loops=1)
  ->  Seq Scan on orders o  (cost=0.00..300.00 rows=500 width=28) (actual time=0.010..1.200 rows=500 loops=1)
  ->  Index Scan using customers_pkey on customers c  (cost=0.29..1.90 rows=1 width=22) (actual time=0.004..0.004 rows=1 loops=500)
        Index Cond: (id = o.customer_id)

The inner index scan reports 0.004 ms, which looks tiny. But it ran 500 times, so its real contribution is roughly 0.004 x 500 = 2 ms. Ignoring loops is one of the easiest ways to misread a plan.

Understanding Common Operations

Operation names vary between database engines. The names below are mostly PostgreSQL terms, with MySQL and generic equivalents noted. Treat the names as a vocabulary for ideas, and always confirm what your specific engine means by them.

Scans (how rows are read from a table)

Sequential Scan (PostgreSQL: Seq Scan; MySQL: type: ALL; SQL Server and others: Table Scan). The database reads every row in the table. This is a full table scan. It is not automatically bad: for small tables, or queries that need a large fraction of the rows, reading everything sequentially is often the cheapest option.

Index Scan. The database walks an index to find matching rows, then visits the table (the “heap” in PostgreSQL) to fetch each full row. It shines when the query is selective, meaning it needs only a small fraction of the table.

Index Only Scan. If the index contains every column the query needs, the database can answer from the index alone without touching the table. In PostgreSQL this depends on the visibility map, so you may still see Heap Fetches in the output if the table was recently modified and not yet vacuumed. MySQL expresses a similar idea as Using index in the Extra column.

Bitmap Index Scan and Bitmap Heap Scan. These work as a pair in PostgreSQL. The Bitmap Index Scan collects the locations of all matching rows from the index into a bitmap. The Bitmap Heap Scan then visits the table pages in physical order. This is a middle ground when too many rows match for a plain index scan to be efficient, but not so many that a sequential scan wins:

Bitmap Heap Scan on orders  (cost=95.30..2100.50 rows=4500 width=28)
  Recheck Cond: (status = 'refunded'::text)
  ->  Bitmap Index Scan on idx_orders_status  (cost=0.00..94.17 rows=4500 width=0)
        Index Cond: (status = 'refunded'::text)

Range Scan (MySQL: type: range). The database reads a contiguous slice of an index, for example created_at BETWEEN ... or id > 1000. In PostgreSQL there is no node called “Range Scan.” A range condition shows up as an Index Scan with a range in its Index Cond.

Joins (how two row sets are combined)

Nested Loop. For each row from the outer input, look up matching rows in the inner input. It is excellent when the outer side is small and the inner side has an index on the join column. It becomes painful when the outer side is large and the inner lookup is expensive.

Hash Join. Build an in-memory hash table from one input, then probe it with the other. It handles large, unsorted inputs well and needs no index, but it needs enough memory (work_mem in PostgreSQL) to avoid spilling to disk. MySQL 8.0.18 and later also supports hash joins, which you will see as Using join buffer (hash join).

Merge Join. Both inputs are sorted on the join key, then walked together like a zipper. It works well when the inputs are already sorted (for example from an index) or when both sides are large. PostgreSQL has this operation. MySQL does not use a general merge join in the same sense.

Other common operations

Sort. Orders rows. Watch the sort method: quicksort and top-N heapsort run in memory, while external merge means the sort spilled to disk. MySQL shows Using filesort, which is a misleading name because it does not necessarily mean a file on disk is used.

Aggregate. Computes things like COUNT, SUM, or GROUP BY results. PostgreSQL has Aggregate, HashAggregate, and GroupAggregate (which requires sorted input). In MySQL, grouping may show up as Using temporary in Extra.

Filter. A condition applied to rows after they have been read. In PostgreSQL it appears as a Filter: line under a node. In MySQL it appears as Using where. A node that reads many rows and removes most of them with a filter is a signal that an index might help.

Limit. Stops after N rows. A Limit above a node that can produce rows in order (such as an index scan) lets the database stop early. A Limit above an expensive Sort still has to sort first, which is why ORDER BY ... LIMIT queries often benefit from an index matching the sort order.

Understanding Cost Estimates

Costs trip up almost everyone at first, so let’s be precise.

Seq Scan on orders  (cost=0.00..5000.00 rows=10000 width=28)

• Startup cost (0.00): the estimated work before the node can return its first row. A Sort has a high startup cost because it must read all input first. A Seq Scan has almost none.
• Total cost (5000.00): the estimated work to return all rows.
• Estimated rows (rows=10000): how many rows the planner expects this node to output.
• Row width (width=28): the estimated average size of each output row in bytes.
• Actual rows (rows=9876): the real count, shown only with ANALYZE.
• Loops (loops=1): how many times the node ran.

Cost is not milliseconds

Cost is measured in arbitrary planner units, not time. In PostgreSQL, one unit is loosely anchored to the cost of reading one page sequentially. A node with cost 5000 is not necessarily slower than one with cost 400 in a way you can convert to seconds. Costs are useful for comparing plans for the same query on the same database, and for spotting which node dominates a plan. They are not a stopwatch.

Estimated vs. actual rows

Comparing rows (estimated) with the actual rows is one of the most valuable checks you can do. If the planner expected 20 rows and got 200,000, it likely chose a plan (such as a nested loop) that is terrible at the real size. Large gaps often point to:

• Outdated statistics (run ANALYZE in PostgreSQL, or ANALYZE TABLE in MySQL)
• Correlated columns the planner treats as independent
• Skewed data, where a few values are very common
• Complex expressions the planner cannot estimate well

Small differences are normal. Order-of-magnitude differences deserve investigation.

How to Identify Performance Problems

When you open a plan, scan for these patterns. None is a guaranteed bug, but each is worth a closer look.

Full table scans on large tables. A Seq Scan (or MySQL type: ALL) on a big table, especially with a Filter that removes most rows. Check Rows Removed by Filter. Reading 190,000 rows to keep 18 is a strong sign an index could help.

Inefficient joins. A nested loop over a large outer set with an expensive inner side, or a hash join that spills to disk (look for Batches greater than 1 in the Hash node). Also check that join columns are indexed on the inner side.

Expensive sorting. A Sort over many rows, particularly with Sort Method: external merge Disk: .... This often means an index matching the ORDER BY could remove the sort, or that work_mem is too low for the workload.

Inaccurate row estimates. Big gaps between estimated and actual rows, as described above. The wrong estimate often causes the wrong join strategy.

Excessive loops. A node with loops=50000 and a small per-loop time can still dominate the query. Multiply before you dismiss it.

Indexes that are not being used. In PostgreSQL, you will see a Seq Scan even though an index exists. In MySQL, check possible_keys versus key: if possible_keys lists an index but key is NULL, the optimizer considered it and rejected it. The reason is usually that the query is not selective enough, the index does not match the filter and sort columns, or a function on the column (like LOWER(email)) prevents index use.

Use Buffers as a tiebreaker. A node reading thousands of pages to return a handful of rows is doing heavy work, regardless of how small its cost looks.

How Indexes Affect Execution Plans

An index is a separate, sorted data structure that lets the database find rows without reading the whole table. When a suitable index exists and the query is selective, the planner can switch from a Seq Scan to an Index Scan and from a Sort to an ordered index read.

When an index can help

• Filtering on a column with highly selective conditions (WHERE customer_id = 4821)
• Join columns, especially foreign keys
• Columns used in ORDER BY, particularly combined with LIMIT
• Composite indexes whose column order matches how you filter and sort

Why the database might ignore your index

An unused index is not necessarily a bug. The planner may skip it because:

• The query returns a large share of the table, so a sequential scan is cheaper.
• The table is small enough that reading it all is trivial.
• The index columns do not match the query (wrong order, or a function wraps the column).
• Statistics suggest the index would not be selective.
• Type mismatches or implicit casts block index use.

Why adding indexes blindly is a bad strategy

Every index has a cost. It consumes disk space, slows down INSERT, UPDATE, and DELETE operations, and adds maintenance work. Indexes on low-selectivity columns (like a boolean) often go unused. The right approach is to let a specific slow query and its plan justify a specific index, then verify the new plan. For a deeper look at design choices, see this practical guide to database indexes in Rails.

Practical Example

Let’s optimize a query you might find behind an “order history” page: the latest paid orders for one customer.

SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 4821
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 10;

The orders table has about 200,000 rows and, so far, no index on customer_id.

Before: the plan without a helpful index

EXPLAIN
SELECT id, status, total_cents, created_at
FROM orders
WHERE customer_id = 4821
  AND status = 'paid'
ORDER BY created_at DESC
LIMIT 10;

Limit  (cost=5500.07..5500.10 rows=10 width=28)
  ->  Sort  (cost=5500.07..5500.12 rows=18 width=28)
        Sort Key: created_at DESC
        ->  Seq Scan on orders  (cost=0.00..5500.00 rows=18 width=28)
              Filter: ((customer_id = 4821) AND (status = 'paid'::text))

Here is how to interpret it:

• The Seq Scan reads the whole table to find roughly 18 matching rows. That is the main cost, about 5500 planner units.
• The Filter line shows both conditions are applied after reading every row.
• The Sort is cheap because only about 18 rows reach it. The real waste is below it.
• The Limit only trims the final output. It cannot save work here because the sort needs all matches first.

The change: a composite index

The query filters by equality on customer_id and status, then orders by created_at. A composite index with the equality columns first and the sort column last matches that shape:

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at);

On a live PostgreSQL production table, you would normally use CREATE INDEX CONCURRENTLY to avoid blocking writes. In Rails, that means algorithm: :concurrently together with disable_ddl_transaction!, which the indexes guide linked above covers.

After: the new plan

Limit  (cost=0.42..24.69 rows=10 width=28)
  ->  Index Scan Backward using idx_orders_customer_status_created on orders  (cost=0.42..44.10 rows=18 width=28)
        Index Cond: ((customer_id = 4821) AND (status = 'paid'::text))

What changed and why it matters:

• The Seq Scan became an Index Scan, and the condition moved from Filter to Index Cond. The database jumps straight to the matching entries instead of reading everything.
• The Sort node disappeared. Because the index is ordered by created_at within each (customer_id, status) pair, PostgreSQL can read it backward to get newest-first order for free.
• The Limit can stop early, since the index delivers rows in the right order. Its cost is a small fraction of the whole scan.
• The estimated total cost fell from roughly 5500 to roughly 44 planner units.

Verify before you celebrate

A lower estimated cost is promising, not proof. Before shipping the index:

1. Run EXPLAIN (ANALYZE, BUFFERS) on realistic data and compare actual time and buffers before and after.
2. Test with different parameter values. A customer with 150,000 orders behaves very differently from one with 18.
3. Run the query several times, since caching affects results.
4. Consider the write cost of the extra index on a busy table.

The new plan should be better for this query shape, but only measurement on your own data can confirm it.

Using EXPLAIN in Rails and Active Record

Rails developers do not have to leave the console to use any of this. The workflow is: capture the SQL, run explain, read the plan, and connect it back to the Ruby code.

Inspect the generated SQL

relation = Order.where(customer_id: 4821, status: "paid")
                .order(created_at: :desc)
                .limit(10)

puts relation.to_sql

Run EXPLAIN from Active Record

Order.where(customer_id: 4821, status: "paid")
     .order(created_at: :desc)
     .limit(10)
     .explain

Active Record sends EXPLAIN to your database and prints the result. On PostgreSQL with a recent Rails version (7.1 and later), you can pass options:

Order.where(customer_id: 4821, status: "paid").explain(:analyze, :buffers)

Remember that :analyze executes the query. That is fine for a SELECT but not something to do casually with writes.

Connecting plans to common Rails problems

• N+1 queries. EXPLAIN analyzes one query at a time, so an N+1 problem will not show up as one bad plan. Instead, you will see your logs full of identical single-row lookups, and each one is fast individually. The fix is eager loading, covered in this guide on how Active Record associations work.
• Missing indexes. A Seq Scan with a large Rows Removed by Filter on a foreign key like customer_id is the classic sign. Rails adds an index for references columns in migrations by default, but older schemas and hand-written columns may lack one.
• Inefficient filtering. Wrapping a column in a function (where("LOWER(email) = ?", ...)) can prevent index use. The plan will show a Filter instead of an Index Cond.
• Unnecessary queries. If a plan is fine but the same query runs on every request, the fix is caching or restructuring, not indexing.

For broader strategies once you have identified the problem, read how to optimize Active Record queries in Rails.

PostgreSQL vs. MySQL

The two databases solve the same problem but present the answer differently. Do not assume terms carry over directly.

A few MySQL access types deserve a quick definition, since they appear in the type column, roughly from best to worst:

• const / system: at most one matching row, found via a primary or unique key.
• eq_ref: one row per join combination using a unique index.
• ref: multiple rows matched via a non-unique index.
• range: an index range scan.
• index: a full index scan, which reads the whole index rather than the whole table.
• ALL: a full table scan.

Other differences to keep in mind: PostgreSQL’s planner exposes more detail about node-level costs and buffer usage, while MySQL’s tabular output is compact but sometimes hides the nested structure of a plan. Plans from MariaDB, SQL Server, Oracle, and SQLite look different again, so treat everything here as PostgreSQL and MySQL behavior, not universal SQL.

Common Mistakes to Avoid

Assuming every sequential scan is bad. A Seq Scan on a small table, or on a query that returns a big share of the rows, can be the best plan. Judge it by table size and selectivity.

Treating cost as actual time. Cost is in planner units. Use it to compare candidate plans, then confirm with measured time.

Adding indexes blindly. Each index slows writes and uses space. Add one only when a specific query’s plan justifies it, and verify the result.

Relying only on estimated plans. Plain EXPLAIN can hide a huge gap between estimated and actual rows. For serious investigations, use EXPLAIN ANALYZE (carefully) and compare.

Optimizing from a single execution. The first run may be slow because the cache is cold. Run the query several times and test different parameter values.

Forgetting about loops. Per-loop numbers on nodes with many loops hide the true cost.

Testing on unrealistic data. A plan on a 500-row development table says little about production. The planner’s choices depend on statistics and data volume, so use data that resembles production when possible.

Running EXPLAIN ANALYZE on writes without thinking. The statement really executes. Use a transaction and a rollback where it is safe.

Best Practices

Use this checklist the next time a query is slow:

1. Reproduce the slow query with realistic parameters, and capture the exact SQL (in Rails, use to_sql or the logs).
2. Start with plain EXPLAIN to see the plan shape without running anything.
3. Use EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL, or EXPLAIN ANALYZE in MySQL, when it is safe to execute the query. Use a transaction and rollback for writes.
4. Read the plan from the innermost node outward. Find the node with the largest actual time or the most buffers.
5. Compare estimated rows with actual rows. Investigate large gaps and refresh statistics if needed.
6. Check loops and multiply per-loop values.
7. Look for large Seq Scans, spilled sorts, and nested loops over large sets.
8. Match indexes to the query: equality columns first, then range or sort columns, and avoid wrapping indexed columns in functions.
9. Make one change at a time, then re-run the plan and compare.
10. Test with several parameter values and run the query more than once.
11. Weigh the write cost of any new index.
12. Re-check after data growth or upgrades, since plans change as data and versions change.

Conclusion

An execution plan is the database explaining its own reasoning. Once you can read it, slow queries stop being mysterious. You can see which node does the most work, whether the planner’s estimates matched reality, and whether an index would actually change the plan.

The most important habits are simple. Compare estimates with actual numbers. Remember that cost is not time. Multiply by loops. Change one thing at a time and measure again. Above all, treat EXPLAIN as evidence rather than a set of rules, because the right plan always depends on your engine, version, schema, statistics, and data.

Start with one slow query in your own project, run EXPLAIN, and walk through it node by node using this guide. The first plan you read carefully will teach you more than any amount of guessing.