SQL EXPLAIN Formatter

Making an EXPLAIN plan readable

A query planner outputs the steps it will take to run a query, but raw EXPLAIN output is dense and often one flat block. This formatter indents the plan into a tree so parent and child operations are obvious, and it flags the expensive steps, sequential scans, large sorts, nested loops over big row counts, so you can see where time actually goes.

When you reach for it

You use it when a query is slow and you need to know why, when deciding whether an index would help, or when a change in data volume suddenly makes a previously fast query crawl. Reading the tree tells you whether the planner chose an index scan or fell back to reading the whole table.

Example

Limit  (cost=0.42..8.44 rows=10)
  -> Index Scan using users_name_idx on users
       (cost=0.42..812.00 rows=10)
       Filter: (active = true)

Seq Scan on orders  (cost=0.00..18234.00 rows=1000000)   <-- costly

The nesting shows the Limit consuming rows from the index scan beneath it, while the flagged sequential scan reads every row of a million-row table.

Common pitfalls

The cost numbers are the planner's estimate, not milliseconds, and they are relative. People misread a high cost as the actual runtime; it is a unitless comparison the planner uses to pick a plan. Also beware estimated versus actual rows: a plan built from stale statistics can estimate ten rows and process a million, which only shows up when you run EXPLAIN ANALYZE to get real counts. A sequential scan is not always bad either; for a small table or a query touching most rows it can beat an index. Judge by row count and context, not the word alone.

FAQ

Are the cost values in milliseconds? No, they are abstract units for comparing plans. Use EXPLAIN ANALYZE for real timing.

Is a Seq Scan always a problem? No. On a small table or when most rows match, scanning can be the fastest choice.

Why do estimated and actual rows differ so much? Stale table statistics. Running ANALYZE refreshes them so the planner estimates better.