If you have spent years reading MySQL EXPLAIN output, your eyes know exactly where to look: the type column, the key column, the rows estimate, and whatever ominous phrases appear under Extra. Then you run your first EXPLAIN in PostgreSQL and get an indented tree of nodes with two cost numbers and no table at all. Both outputs describe the same idea, an execution plan, but they speak different languages, and the differences are worth learning properly rather than by frustration.
I moved from reading MySQL plans daily to reading PostgreSQL plans daily, and this is the translation guide I wish someone had handed me.
What MySQL EXPLAIN actually tells you
Classic MySQL EXPLAIN produces one row per table in the join, in join order. The columns that carry most of the signal are type (the access method), key and key_len (which index, and how much of it), rows (estimated rows examined per lookup), filtered (estimated percentage surviving the remaining WHERE conditions), and Extra (a grab bag of execution notes).
EXPLAIN
SELECT o.id, o.total_amount
FROM orders AS o
WHERE o.customer_id = 42
AND o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;The type column has a well-known quality ladder: const and eq_ref are excellent, ref and range are normal, index means a full index scan, and ALL means a full table scan. Extra is where the real warnings live. Using filesort means an explicit sort operation, Using temporary means an intermediate table, Using index means the query is covered by the index, and Using index condition means index condition pushdown is filtering rows inside the storage engine before fetching them.
MySQL 8.0.16 added FORMAT=TREE, and 8.0.18 added EXPLAIN ANALYZE, which actually executes the statement and reports, per iterator, the estimated cost and rows next to the actual time for first row and all rows, actual rows, and loop count. If you are on MySQL 8.x and still reading only the tabular format, you are leaving the best diagnostic on the table: the estimate-versus-actual comparison is where regressions become visible.
What PostgreSQL EXPLAIN actually tells you
PostgreSQL EXPLAIN always shows a tree of plan nodes, innermost operations indented deepest. Every node carries two cost figures, startup cost and total cost, plus estimated rows and row width. Plain EXPLAIN does not execute anything. EXPLAIN ANALYZE executes the query for real and adds actual time (also split into startup and total), actual rows, and loops per node. Adding BUFFERS reports block-level I/O: shared hit means the page was in shared buffers, shared read means it came from the kernel or disk, plus dirtied, written, and temp blocks for spills.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total_amount
FROM orders AS o
WHERE o.customer_id = 42
AND o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;Two reading habits matter more than everything else. First, actual rows are averaged per loop, so an inner index scan showing rows=1 with loops=50000 touched fifty thousand rows in total; forgetting the multiplication is the classic PostgreSQL plan-reading mistake. Second, always look for the node where estimated rows and actual rows diverge first, because every decision above that node was made with bad information. On PostgreSQL 16 and 17 you must ask for BUFFERS explicitly, and I recommend always doing so; timing without I/O context is half a diagnosis. A query plan analyzer that flags estimate divergence and hot nodes automatically saves a lot of squinting at indentation.
The cost models are not comparable numbers
Both engines produce cost numbers, and neither is in milliseconds. PostgreSQL costs are expressed in units anchored to seq_page_cost = 1.0, with random_page_cost, cpu_tuple_cost, and friends layered on; a cost of 5000 means nothing except in comparison to another plan for the same query on the same settings. MySQL costs come from its own cost model, with constants stored in the server_cost and engine_cost tables, and are similarly unitless.
The practical consequence: never compare a MySQL cost to a PostgreSQL cost, and be careful comparing costs across servers with different settings even within one engine. What is comparable, and what you should baseline during a migration, is actual execution evidence: real timings, real row counts, and real I/O for the same workload on each engine.
A translation table for common access types
These correspondences are approximate, because the executors genuinely differ, but they are how I map one mental model onto the other.
| MySQL EXPLAIN | Rough PostgreSQL equivalent |
|---|---|
| type = const / eq_ref | Index Scan on a unique index (single-row lookup, often inner side of a Nested Loop) |
| type = ref | Index Scan or Bitmap Index Scan on a non-unique index |
| type = range | Index Scan with a range Index Cond, or Bitmap Heap Scan for wider ranges |
| type = index (full index scan) | Index Only Scan when covered, otherwise a full-index Index Scan |
| type = ALL | Seq Scan |
| type = index_merge | BitmapAnd / BitmapOr feeding a Bitmap Heap Scan |
| Extra: Using index | Index Only Scan |
| Extra: Using index condition | Index Cond evaluated in the index, versus Filter applied at the node |
| Extra: Using filesort | Sort node (quicksort in memory, external merge on disk) |
| Extra: Using temporary | HashAggregate, Materialize, or Sort node depending on the query |
One warning about the last two rows: MySQL's Using filesort sounds scarier than it is, and PostgreSQL's Sort node looks calmer than it is. Both mean the same thing, an explicit sort that scales with input size, and both deserve the same question: could an index have provided this order?
What each output hides
MySQL's tabular EXPLAIN hides the most important number in performance work: actual rows. The rows column is an estimate, filtered is an estimate of an estimate, and until you run EXPLAIN ANALYZE you have no evidence, only forecasts. It also gives you no I/O visibility at all; there is no equivalent of BUFFERS, so a plan that reads a million pages from disk and a plan served entirely from the buffer pool can look identical. And Extra compresses genuinely different situations into terse phrases; Using where tells you a filter exists, not how much it discards.
PostgreSQL's output has its own blind spots. Per-loop row averaging can hide skew: rows=10 with loops=1000 might mean a uniform ten rows per iteration or one iteration that returned ten thousand. Plain EXPLAIN without ANALYZE shares the same forecast problem as MySQL, and even with ANALYZE, buffer counts are absent unless you asked. EXPLAIN ANALYZE also executes the statement, so an EXPLAIN ANALYZE on a DELETE really deletes; wrap it in a transaction and roll back. Finally, both engines share one structural blind spot: EXPLAIN shows you one execution of one query, and says nothing about how often it runs or how it behaved yesterday, which is why plan reading needs slow query monitoring around it to matter in production.
A migration reading drill that works
When we moved workloads from MySQL to PostgreSQL, the most effective habit was a side-by-side drill. Take your top twenty query families by total time. For each, capture MySQL EXPLAIN ANALYZE output and PostgreSQL EXPLAIN (ANALYZE, BUFFERS) output on production-shaped data, and answer three questions: does each engine use the index you expected, where do estimates diverge from actuals, and where does the time actually go? The exercise surfaces schema assumptions that do not port, missing statistics on the PostgreSQL side, and indexes that were only ever there to serve a MySQL-specific plan shape. It is boring, and it prevents the week-one incident.
Where MonPG fits once you are on PostgreSQL
Plan reading is a point-in-time skill; production needs the time dimension. Once your workload lands on PostgreSQL, MonPG keeps the evidence flowing: query family history from pg_stat_statements, plan context for the queries that matter, and buffer and timing trends so that a plan flip shows up as a visible break in the graph rather than a mystery ticket. MonPG monitors PostgreSQL only, so everything in it speaks the vocabulary from this article, with nodes, buffers, and estimates rather than lowest-common-denominator metrics. If you want the plan tree itself made readable, with hot nodes and estimate divergence highlighted, start with the PostgreSQL query plan analyzer; it turns the reading drill above into a routine instead of an art.