Making PostgreSQL Plans Readable

A raw execution plan is not a story meant to be read from top to bottom. A better approach is to treat the plan as relational data: nodes, edges, conditions, estimates, actual runtime values, and buffer statistics. After that, "top 3 estimate errors", "most expensive nodes", and "data-flow narrative" are just views over those tables.

PostgreSQL EXPLAIN is honest, but it is not always friendly. A complex query can quickly become dozens of nested nodes: Nested Loop, Hash Join, Bitmap Heap Scan, Sort, and Aggregate. If you only read the indented text from the first line down, you may see many nodes but still miss the real issue.

The way I prefer to unfold a plan is simple: first treat the plan as structured data, then load it into a small relational model. The human-facing report is not a one-off explanation. It is a set of reusable views. Those views should answer four questions:

1. Where does the data come from, and does each step expand or reduce it?
2. How many rows did the optimizer expect, and how many rows actually appeared?
3. Where are the filter conditions, join conditions, and index conditions applied?
4. Which nodes are expensive, and what risk signals do they show?

Core idea: a plan is also relational tables

A PostgreSQL plan looks like a tree, but analysis needs a queryable data model. Once the plan is decomposed into tables, many explanations stop being special logic and become SQL.

plan_node(
  plan_id,
  node_id,
  parent_id,
  node_type,
  relation_name,
  alias,
  startup_cost,
  total_cost,
  plan_rows,
  plan_width,
  actual_startup_time,
  actual_total_time,
  actual_rows,
  actual_loops
)

plan_condition(
  plan_id,
  node_id,
  condition_kind, -- Index Cond, Filter, Hash Cond, Join Filter, Recheck Cond
  expression
)

plan_buffer(
  plan_id,
  node_id,
  shared_hit,
  shared_read,
  shared_dirtied,
  shared_written,
  temp_read,
  temp_written
)

With this layer, reading a plan becomes natural. Instead of searching through indented text, you query the plan tables.

-- top 3 row estimate errors
create view plan_top_row_error as
select
  node_id,
  node_type,
  relation_name,
  plan_rows,
  actual_rows * actual_loops as actual_total_rows,
  greatest(
    (actual_rows * actual_loops) / nullif(plan_rows, 0),
    plan_rows / nullif(actual_rows * actual_loops, 0)
  ) as row_error
from plan_node
order by row_error desc nulls last
limit 3;

-- most expensive nodes
create view plan_most_expensive as
select
  node_id,
  node_type,
  relation_name,
  actual_total_time * actual_loops as total_time
from plan_node
order by total_time desc
limit 10;

This is why a readable plan should not be only pretty HTML. Pretty HTML is the presentation layer. The valuable part is the relational model underneath. With it, the same plan can have many views: performance view, estimate-error view, join view, scan view, buffer view, and condition-pushdown view.

JSON is the input, relational tables are the model

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) is a good input format because it preserves node fields and tree structure. But JSON is still only the input. If all analysis logic is written as ad hoc JSON traversal, the result is usually a formatter that answers only fixed questions.

A better boundary is: JSON carries the raw plan; the relational model carries the analysis object. Once nodes, edges, conditions, buffers, JIT data, and worker data are in tables, explaining the plan becomes querying those tables. The page is just the rendering of the views. It should not own the core analysis logic.

Views are more reliable than hand-written explanation

Estimate error, expensive nodes, condition pushdown, and repeated execution counts are not primarily natural-language problems. They are relational queries. Estimate error comes from plan_node. Condition pushdown comes from plan_condition. Buffer hotspots come from sorting plan_buffer.

This also makes explanations reviewable. If the report says "this Nested Loop may be risky", the reader should be able to trace that back to a specific view result, field, and raw JSON node.

Facts and judgment should be separate

Plan tools often make the same mistake: they turn guesses into facts. Seeing Nested Loop and saying it is slow, or seeing Seq Scan and saying an index is missing, is not rigorous enough.

A relational model helps separate three layers:

• fact layer: how many times a node ran, how many rows it produced, how many buffers it read
• derived layer: estimate error, time percentage, whether a condition became an index condition
• judgment layer: whether the risk likely comes from statistics, access path, join order, memory, or I/O

These layers should not be mixed. The first two layers are good SQL views. The third can be prose, but it should point back to the view output.

A practical view set

If I were building a human-readable PostgreSQL plan page or command-line tool, I would not hard-code all logic in the renderer. I would generate these views first and let the page display them:

1. plan_summary: one sentence saying where the query mainly spent time.
2. plan_flow: 5 to 10 key steps in data-flow order.
3. plan_top_row_error: the top 3 row-estimate mistakes.
4. plan_most_expensive: expensive nodes by actual time, loops, and buffers.
5. plan_conditions: index conditions, filters, and join conditions separated.
6. plan_raw: the complete JSON, kept for traceability.

The goal is not to replace EXPLAIN. The goal is to turn a plan from "a piece of output" into "a queryable dataset". For a database engineer, the plan remains the evidence. The unfolded report is only a reading interface built on top of that evidence.