EXPLAIN, explained
What each step of a query plan does, when the planner picks it, and what to change when it’s the slow part. Every example is a real plan from a real server.
Paste a plan, see where the time goesThe free plan visualizer draws EXPLAIN output as a tree and marks the slowest step. Nothing leaves your browser.Open the visualizer →
Reading the numbers
- Buffers: shared hit, read, dirtied, written in PostgreSQL EXPLAINThe Buffers line counts the 8 kB pages a plan step touched: hit means the page was already in PostgreSQL’s shared buffers, read means it had to be fetched from the operating system. It’s the best measure of how much data a query really reads.
- cost and rows estimates in PostgreSQL EXPLAINcost is the planner’s guess at how expensive a step is, in arbitrary units where reading one page in sequence costs 1. rows is its guess at how many rows the step returns. When the guessed rows are far from the actual rows, the planner may have picked the wrong plan.
- Heap Blocks: lossy in PostgreSQL EXPLAINA lossy heap block is a table page the bitmap remembered only as “something on this page matches”, because the bitmap ran out of work_mem. PostgreSQL then rechecks every row on that page, and Rows Removed by Index Recheck counts the ones that didn’t match.
- Heap Fetches in PostgreSQL EXPLAINHeap Fetches counts the rows an Index Only Scan had to look up in the table after all, because their page wasn’t marked all-visible. VACUUM sets those marks; a high count means the scan is doing an ordinary index scan’s work.
- loops and actual time in PostgreSQL EXPLAIN ANALYZEIn EXPLAIN ANALYZE, actual time and rows are averages for one execution of the step, and loops says how many times it ran. Multiply time and rows by loops to get the step’s real total: 0.287 ms × 1,000 loops is 287 ms.
- Rows Removed by Filter in PostgreSQL EXPLAINRows Removed by Filter is how many rows a plan step read and then threw away because they didn’t match a condition it couldn’t use an index for. A big number next to a small rows count means the step read far more than it returned.
PostgreSQL plan nodes
- Aggregate in PostgreSQL EXPLAINAggregate computes count(), sum(), avg() and friends over all of its input and returns one row. In a parallel plan it’s split in two: Partial Aggregate in each process, Finalize Aggregate in the leader. With GROUP BY you’ll see HashAggregate or GroupAggregate instead.
- Append in PostgreSQL EXPLAINAppend runs several child plans one after another and returns all their rows: one child per partition of a partitioned table, or per branch of a UNION ALL. The thing to check is how many children there are; partition pruning should have removed the ones that can’t match.
- Bitmap Heap Scan in PostgreSQL EXPLAINA Bitmap Heap Scan reads the table pages that a Bitmap Index Scan marked, in physical order, each page once. It’s the planner’s middle ground between an index scan and a full table scan; it slows down when the bitmap goes lossy or the matches cover most of the table.
- Bitmap Index Scan in PostgreSQL EXPLAINA Bitmap Index Scan reads an index and marks the table locations of every match in an in-memory bitmap. It returns no rows itself: the Bitmap Heap Scan above it uses the bitmap to read those table pages in order.
- CTE Scan in PostgreSQL EXPLAINA CTE Scan reads the stored result of a WITH query that PostgreSQL computed once, in full. Since PostgreSQL 12 most WITH queries are folded into the main query instead; a CTE Scan means it was materialised, and conditions outside it can’t use the tables’ indexes.
- Gather in PostgreSQL EXPLAINGather is where a parallel plan comes back together: it starts the parallel workers, runs the plan below it in each of them (and in your own session), and passes their rows up in whatever order they arrive.
- Gather Merge in PostgreSQL EXPLAINGather Merge collects rows from parallel workers like Gather does, but each worker hands over its rows already sorted and the leader merges them, so the output stays in order. You’ll usually find a Sort in each worker beneath it.
- GroupAggregate in PostgreSQL EXPLAINGroupAggregate does GROUP BY on input that’s already sorted by the group key: it adds up rows until the key changes, outputs that group and starts the next. It needs almost no memory, but the sort or index scan that feeds it can be expensive.
- Hash in PostgreSQL EXPLAINThe Hash node builds the in-memory hash table that a Hash Join looks rows up in. Its Buckets, Batches and Memory Usage line tells you how big the table got and whether it had to be split into batches on disk.
- Hash Join in PostgreSQL EXPLAINA Hash Join loads the smaller input into an in-memory hash table keyed on the join columns, then reads the larger input once and looks each row up. It’s the usual plan for joining many rows on an equality; it slows down when the hashed side doesn’t fit in memory.
- HashAggregate in PostgreSQL EXPLAINHashAggregate does GROUP BY (and often DISTINCT) by keeping one entry per group in a hash table. It needs no sorted input, but memory grows with the number of groups; past work_mem × hash_mem_multiplier it spills to disk in batches.
- Incremental Sort in PostgreSQL EXPLAINAn Incremental Sort takes rows that are already sorted on the first part of the sort key and sorts each group of equal values on the rest. With a LIMIT it can stop after a few groups instead of sorting everything. It appeared in PostgreSQL 13.
- Index Only Scan in PostgreSQL EXPLAINAn Index Only Scan answers the query from the index alone, without reading the table, when every column it needs is in the index. It still visits the table for rows on pages not yet marked all-visible by VACUUM; Heap Fetches counts those visits.
- Index Scan in PostgreSQL EXPLAINAn Index Scan looks up matching entries in an index and fetches each row from the table, in index order. It’s fast for a few rows or when the order saves a sort; it gets slow when it fetches many rows scattered across the table.
- Insert, Update, Delete and Merge (ModifyTable) in PostgreSQL EXPLAINInsert on, Update on, Delete on and Merge on are the ModifyTable node: the top of every data-changing plan. The nodes below find the rows; ModifyTable writes them, updates indexes and fires triggers. Foreign-key checks are listed separately under “Trigger for constraint”.
- Limit in PostgreSQL EXPLAINLimit stops asking its child for rows once it has enough, so nodes below it often stop early. That makes LIMIT queries fast when the first rows are easy to find, and very slow when the planner bets on finding matches early and loses.
- Materialize in PostgreSQL EXPLAINMaterialize stores its child’s rows the first time they’re read, so that a Nested Loop can scan them again and again without re-running the child. It’s cheap in itself; when it appears with huge loops counts, the join around it is the problem.
- Memoize in PostgreSQL EXPLAINMemoize sits on the inner side of a Nested Loop and caches the inner rows for each join key it has seen, so repeated keys skip the lookup. It appeared in PostgreSQL 14. Its Hits and Misses line tells you at a glance whether the cache earned its place.
- Merge Append in PostgreSQL EXPLAINMerge Append combines several child plans that each return rows in the same order, merging them so the output stays sorted. Over UNION ALL branches or partitions with matching indexes, ORDER BY … LIMIT reads only a few rows from each.
- Merge Join in PostgreSQL EXPLAINA Merge Join reads two inputs that are both sorted on the join key and walks them side by side, like merging two sorted lists. It shines when indexes already provide the order; when it needs big Sort nodes underneath, it’s often slower than a hash join.
- Nested Loop in PostgreSQL EXPLAINA Nested Loop join runs its inner side once for every row from its outer side. With few outer rows and an index on the inner table it’s the fastest join there is; when the planner underestimates the outer rows, it’s the classic cause of a query that suddenly takes seconds.
- Parallel Seq Scan in PostgreSQL EXPLAINA Parallel Seq Scan is a sequential scan split between several processes: each one reads a share of the table’s pages and filters them. It always sits under a Gather or Gather Merge, and its rows and times are averages per process.
- Seq Scan in PostgreSQL EXPLAINA Seq Scan reads every row of a table in the order it’s stored and keeps the rows that match. It’s the right plan for small tables and for queries that return a large share of the rows; on a big table that returns a handful, it usually means a missing index.
- Sort in PostgreSQL EXPLAINA Sort node reads all of its input and returns it in order. Its Sort Method line says whether it fitted in work_mem (quicksort), kept only the top rows for a LIMIT (top-N heapsort), or spilled to temporary files (external merge).
- Subquery Scan in PostgreSQL EXPLAINA Subquery Scan reads the rows of a subquery that PostgreSQL couldn’t merge into the outer query, and applies the outer query’s conditions or column list to them. A Filter on it means those conditions only ran after the subquery had produced everything.
- Unique in PostgreSQL EXPLAINUnique removes duplicate rows from input that’s already sorted, by comparing each row with the one before. It implements DISTINCT and DISTINCT ON when the planner sorts (or reads an index) instead of hashing. It’s cheap; the sort or scan beneath it often isn’t.
- WindowAgg in PostgreSQL EXPLAINWindowAgg computes window functions such as row_number(), rank() and running sum() over rows sorted by PARTITION BY and ORDER BY. It needs a Sort or an index for that order, and since PostgreSQL 15 it can stop early on conditions like rn <= 3.
MySQL EXPLAIN
- Full table scan (type: ALL) in MySQL EXPLAINtype: ALL means MySQL reads every row of the table. For small tables, or queries that return a large share of the rows, that’s the cheapest plan. For a selective query on a big table it usually means a missing or unusable index.
- Using filesort in MySQL EXPLAINMySQL had to sort the rows itself because it couldn’t read them in ORDER BY order from an index. The name is historical: the sort happens in memory unless it outgrows sort_buffer_size. It matters when many rows are sorted to return a few.
- Using index condition in MySQL EXPLAINIndex condition pushdown: InnoDB checks parts of the WHERE clause against the index entry before fetching the full row, so rows that can’t match are never read. It’s a good sign, not a problem. It means the index is only partly used for the search.
- Using join buffer (hash join) in MySQL EXPLAINMySQL joined two tables without an index on the join columns: it loaded one side into an in-memory hash table and scanned the other against it. Fine for reports over whole tables; for selective lookups it means scanning a large table that an index would let MySQL skip.
- Using temporary in MySQL EXPLAINMySQL builds an internal temporary table to finish the query, most often to group rows that don’t arrive in GROUP BY order. It lives in memory unless it outgrows tmp_table_size. Removing it isn’t always faster; check with EXPLAIN ANALYZE.
- Using where in MySQL EXPLAINMySQL checks a WHERE (or join) condition on each row after reading it, and throws away rows that don’t match. On its own that’s normal. Next to type ALL or index it means MySQL reads many rows to keep a few.