Query plan visualization tools

Capsule description

Plans of 5-15 lines read well in plain text. Plans of 30+ lines with nested joins, CTEs, subqueries, and parallel nodes are unreadable to the human eye. Trying to read them in the terminal is the recipe for missing important details — a row mismatch hidden at nesting level 4, a node with loops=10000 lost among 50 lines.

The industry solved this a while ago: there are 3-4 web and desktop tools that take the EXPLAIN output (as text or JSON) and visualize it as an interactive color-coded tree that highlights the problematic nodes. Any senior DBA or backend dev uses them for complex plans, not out of laziness — because humanly you can't keep a mental map of 50 indented lines.

In this capsule you'll get to know the three main ones:

• explain.depesz.com — the simplest, made by Hubert "depesz" Lubaczewski. Paste text, see a color-coded table. The de facto standard in the PG community.
• explain.dalibo.com — tree/diagram-style visualization, richer visually. Handles JSON.
• pgMustard — a paid tool (with a limited free tier) with automated problem analysis. The most "opinionated".

You'll learn when to go to each one, how to prepare the plan for it correctly, and how to interpret the information they add vs what you already read in text. By the end, large plans stop being a blocker — you paste them into the tool and diagnose in 30 seconds what would take you 10 minutes in text.

Mental model: visualization vs analysis

The tools do two different things:

1. Visualization: they convert the plan's text (or JSON) into a more readable graphical representation — a tree, a colored table, a diagram.
2. Analysis: they automatically detect problematic patterns and flag them: row mismatches, expensive scans, sorts that spill, nodes with high loops.

The three tools do both, but with a different emphasis:

Practical rule of the module: plans <15 lines, in text. Plans >15 lines, to a tool. It's not laziness — it's the standard.

explain.depesz.com: the simplest and most standard

Made by Hubert Lubaczewski (author of the "Explaining the unexplainable" series). It's the oldest and most-used tool in the PostgreSQL community.

How to use it

1. Go to https://explain.depesz.com/.
2. Paste the output of EXPLAIN (ANALYZE, BUFFERS) <query> (plain text, not JSON).
3. Click "Submit".
4. It generates a permanent link (you can share it). It shows you a table with each node of the plan, colored according to problems.

What it adds vs plain text

                                                            QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=12.34..1850.20 rows=20 width=68) (actual time=0.234..1.812 rows=415 loops=1)
   Hash Cond: (b.author_id = a.id)
   Buffers: shared hit=812
   ->  Seq Scan on books b  (cost=0.00..1620.00 rows=100000 width=64) ...
   ->  Hash  ...
         ->  Seq Scan on authors a  ...
               Filter: (name = 'tolkien'::text)
               Rows Removed by Filter: 4999
 Planning Time: 0.412 ms
 Execution Time: 1.901 ms

In depesz, the same thing becomes something like:

[Table with columns and colors]
| #  | exclusive | inclusive | rows x | rows | loops | node                              |
|----|-----------|-----------|--------|------|-------|------------------------------------|
| 1  | 0.097ms   | 1.812ms   |  20.8x |  415 | 1     | Hash Join                         |
| 2  | 1.245ms   | 1.245ms   |  1.0x  | 100k | 1     | └─ Seq Scan on books              |
| 3  | 0.000ms   | 0.142ms   |  1.0x  | 1    | 1     | └─ Hash                           |
| 4  | 0.140ms   | 0.140ms   |  1.0x  | 1    | 1     |    └─ Seq Scan on authors         |

And colored cells:

• Green: all good, within expectations.
• Yellow: warning (moderate row mismatch, moderately expensive scan).
• Red: serious problem (large row mismatch, node dominant in time, high loops).
• rows x: divergence between estimated and actual (actual / estimated). 20.8x = the planner underestimated by 20x.
• exclusive: the node's time alone, without its children.
• inclusive: the node's time + all its children.

The most useful thing about depesz:

• It identifies row mismatches automatically and colors them. In the example above, the 20.8x is in yellow/red — it shouts at you "the planner got it wrong here".
• It computes exclusive vs inclusive time. In text, the actual time is inclusive (includes children). depesz separates them, so you see where CPU is really spent.
• Permanent link. Share it in chat, in a PR, in a ticket. The recipient sees the same colored table as you. An immediate common language.
• Optional anonymization. If your plan has sensitive table/column names, depesz can anonymize them before generating the public link.

Privacy caveat

depesz stores the plans on its server. Don't paste plans with sensitive data (column names that reveal confidential info, SQL comments with secrets, etc.). Three options:

1. Anonymize before uploading (depesz has a checkbox).
2. Use the self-hosted version of depesz (it's open source: github.com/depesz/Pg-explain).
3. Use explain.dalibo.com which has a "don't store" mode (it runs in the browser).

For plans from your test database or from demos, it's not a problem. For production plans with sensitive info, consider self-hosted or dalibo.

explain.dalibo.com: tree-style visualization

Made by Dalibo (a French consultancy specialized in PostgreSQL). It's richer visually than depesz: it represents the plan as an interactive tree, with each node as an expandable "card".

How to use it

1. Go to https://explain.dalibo.com/.
2. Paste the plan. For the best experience, use EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) <query> — the tool understands both but JSON gives it more data.
3. Click "Submit".
4. It generates the visual tree.

What it adds vs depesz

• Hierarchical tree view: you see the plan as a 2D diagram, with nodes connected by lines. For plans of 50+ lines with many nesting levels, it's far more readable.
• Expandable cards: each node is a card with all its metrics (cost, rows, time, buffers). Click to expand and see details.
• Richer problem detection: badges on each node based on metrics — "Slowest node", "Largest node", "Costliest node", "Bad estimate".
• "Don't store" mode: it processes the plan in the browser, doesn't save anything on the server. Better for plans with sensitive info.
• It supports parallel plans better (it renders them with visually separate workers).

When to use dalibo instead of depesz

• Plans with many nesting levels (nested joins, subqueries).
• Parallel plans (several workers).
• When you want to see "the tree" to understand the structure.
• Plans with sensitive info (browser-only mode).

When to use depesz instead of dalibo

• Medium plans where the colored table is enough.
• When you want a permanent link that's easy to share.
• When you only care about seeing row mismatches and per-node times, not the structure.

In practice, experienced DBAs use them complementarily. depesz for quick analysis, dalibo when you need to "see" the full plan.

pgMustard: deep automated analysis

Made by an independent team. It's a commercial tool (with a free tier limited to a few analyses per month). It's the most "opinionated" — it doesn't just show you the plan, it automatically suggests problems and links to documentation on how to fix them.

How to use it

1. Go to https://www.pgmustard.com/.
2. Paste the plan (ideally JSON).
3. It shows you an analysis with cards like:
   • "Index Scan with high heap fetches" → suggests a covering index.
   • "Bad estimate" → suggests ANALYZE or STATISTICS.
   • "Sort spilled to disk" → suggests raising work_mem.
   • "N+1 pattern detected" → suggests eager loading.

What it adds vs depesz/dalibo

• Pre-digested analysis. Instead of "there's a problem here, you figure out which one", it tells you "this node has this problem, the typical solutions are X, Y, Z". It reduces the cognitive load.
• Links to documentation. Each problem comes with links to docs / blog posts that explain the pattern.
• Detection of complex patterns that would require experience to recognize (cardinality estimation issues, JIT thrashing, work_mem spills).

When pgMustard is worth paying for

• Teams where several devs analyze plans but aren't DBAs (the tool covers the expertise gap).
• CI/CD pipelines where you want automated plan analysis (pgMustard has an API).
• When the cost of manual analysis is high (expensive senior devs analyzing repetitive plans).

For individual devs or small teams with one dev who reads plans well, depesz + dalibo cover the need. pgMustard is for scale.

Recommended workflow: when to use each one

┌──────────────────────────────────────────────────────────┐
│  A plan of how many lines?                               │
└─────────────────┬────────────────────────────────────────┘
                  │
       ┌──────────┼──────────┐
       │          │          │
      <15        15-50      >50 or very nested
       │          │          │
       ▼          ▼          ▼
   Text        depesz      dalibo
                            │
                            ▼
                      Still confusing?
                      Want automatic tips?
                            │
                            ▼
                       pgMustard

Practical rule:

1. Short and obvious plan → I read it in text, saving a click.
2. Medium plan or one I'm going to share → depesz, permanent link.
3. Large / nested plan → dalibo, tree view.
4. Not sure which problem to look for → pgMustard if I have access.

Team workflow:

• Capture: EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) <query> in a file.
• Paste in depesz → link → share in chat with a preliminary analysis.
• If the analysis needs more detail, paste in dalibo from the original JSON.
• If no one on the team can diagnose, pgMustard / DBA / consultant.

How to capture plans for tools

Plain text (for depesz)

\o /tmp/plan.txt
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>;
\o

Or in psql:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>;

And copy the full output (including QUERY PLAN, Planning Time, Execution Time).

JSON (for dalibo and pgMustard)

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) <query>;

Output:

[
  {
    "Plan": {
      "Node Type": "Hash Join",
      ...
    },
    "Planning Time": 0.412,
    "Execution Time": 1.901
  }
]

Copy the full array (the [...] brackets).

From the app (SQLAlchemy logs)

If the query comes from your FastAPI app with SQLAlchemy and you want to capture the exact plan that runs:

from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(text(
        "EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) "
        + str(your_query.compile(compile_kwargs={"literal_binds": True}))
    ))
    plan_json = result.scalar()
    print(plan_json)

(Tip: in production, better to use auto_explain — module 5.)

Real example: a large plan analyzed in each tool

Let's take a 35-line plan (typical of a query with 3 joins + sort + limit) and view it in each tool.

The plan

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT b.id, b.title, a.name as author, COUNT(r.id) as review_count
FROM books b
JOIN authors a ON a.id = b.author_id
LEFT JOIN reviews r ON r.book_id = b.id
WHERE a.name = 'tolkien' AND b.published_year > 1950
GROUP BY b.id, b.title, a.name
ORDER BY review_count DESC
LIMIT 20;

Limit  (cost=2540.85..2540.90 rows=20 width=58) (actual time=18.412..18.420 rows=20 loops=1)
  Output: b.id, b.title, a.name, (count(r.id))
  Buffers: shared hit=2120
  ->  Sort  (cost=2540.85..2541.40 rows=220 width=58) (actual time=18.410..18.414 rows=20 loops=1)
        Output: b.id, b.title, a.name, (count(r.id))
        Sort Key: (count(r.id)) DESC
        Sort Method: top-N heapsort  Memory: 28kB
        Buffers: shared hit=2120
        ->  GroupAggregate  (cost=2480.00..2535.00 rows=220 width=58) (actual time=15.234..18.012 rows=210 loops=1)
              Output: b.id, b.title, a.name, count(r.id)
              Group Key: b.id, b.title, a.name
              Buffers: shared hit=2120
              ->  Sort  (cost=2480.00..2485.50 rows=2200 width=54) (actual time=15.215..16.123 rows=2150 loops=1)
                    Output: b.id, b.title, a.name, r.id
                    Sort Key: b.id, b.title, a.name
                    Sort Method: quicksort  Memory: 152kB
                    Buffers: shared hit=2120
                    ->  Hash Right Join  (cost=15.20..2380.00 rows=2200 width=54) (actual time=0.512..14.234 rows=2150 loops=1)
                          Output: b.id, b.title, a.name, r.id
                          Hash Cond: (r.book_id = b.id)
                          Buffers: shared hit=2120
                          ->  Seq Scan on public.reviews r  (cost=0.00..2120.00 rows=100000 width=8) (actual time=0.012..6.512 rows=100000 loops=1)
                                Output: r.id, r.book_id
                                Buffers: shared hit=1310
                          ->  Hash  (cost=14.95..14.95 rows=20 width=46) (actual time=0.485..0.486 rows=22 loops=1)
                                Output: b.id, b.title, a.name
                                Buckets: 1024  Batches: 1  Memory Usage: 11kB
                                Buffers: shared hit=812
                                ->  Hash Join  (cost=12.32..14.95 rows=20 width=46) (actual time=0.245..0.482 rows=22 loops=1)
                                      Output: b.id, b.title, a.name
                                      Hash Cond: (b.author_id = a.id)
                                      Buffers: shared hit=812
                                      ->  Seq Scan on public.books b  (cost=0.00..1870.00 rows=420 width=22) (actual time=0.012..0.412 rows=415 loops=1)
                                            Output: b.id, b.title, b.author_id, b.published_year
                                            Filter: (b.published_year > 1950)
                                            Rows Removed by Filter: 99585
                                            Buffers: shared hit=810
                                      ->  Hash  (cost=12.30..12.30 rows=1 width=8) (actual time=0.142..0.142 rows=1 loops=1)
                                            Output: a.id, a.name
                                            Buckets: 1024  Batches: 1  Memory Usage: 9kB
                                            Buffers: shared hit=2
                                            ->  Seq Scan on public.authors a  (cost=0.00..12.30 rows=1 width=8) (actual time=0.118..0.140 rows=1 loops=1)
                                                  Output: a.id, a.name
                                                  Filter: (a.name = 'tolkien'::text)
                                                  Rows Removed by Filter: 4999
                                                  Buffers: shared hit=2
Planning Time: 0.812 ms
Execution Time: 18.512 ms

35 lines. Indentation up to 5 levels. By eye it's hard.

In depesz

You paste the plan, it generates a table with:

• Most expensive node (red): Seq Scan on books — it scans 100k rows, discards 99,585 (99.5%). A filter without an effective index.
• Row mismatch (yellow): some nodes with minor divergences.
• Exclusive time: it tells you exactly how much each node contributed to the total time.

In 30 seconds you identify the bottleneck (Seq Scan on books with a filter not covered by an index).

In dalibo

Tree view:

• You see the 4 nesting levels as a diagram.
• Each node is a card with its metrics.
• "Largest node" badge on Seq Scan on reviews (100k rows scanned).
• "Slowest node" badge on Seq Scan on books (3.6ms exclusive).
• Click on any node for details.

To understand the structure ("what comes from where and why?"), dalibo is superior.

In pgMustard

Automatic analysis with cards:

• "Inefficient filter": the Filter on books removed 99.6% of the rows read. Suggestion: a composite index on (author_id, published_year) or an expression index.
• "Bad estimate": it estimates 420 rows in the Seq Scan, real 415. ✅ Good (not flagged).
• "Sort method: top-N heapsort": ✅ optimal, no spill.

It saves you the manual analysis.

Why does this matter in real work?

1. Large plans are the rule, not the exception.

Any query with >2 joins, views, or subqueries generates plans of 30+ lines. If you don't use tools, you'll lose time on every analysis.

2. Sharing with colleagues.

"Look at this plan: [depesz link]" is 100x more useful than pasting 50 lines of text into Slack. Everyone sees the same colored table, everyone discusses the same thing.

3. Code review of PRs that change queries.

When a PR modifies a critical query, ideally it includes before/after plans in the description. Linking plans to depesz/dalibo in the PR is standard in serious teams.

4. Onboarding new devs.

A dev who's just learning to read plans has a faster curve with visual tools than with plain text. depesz/dalibo speed up the familiarization.

5. Incident documentation.

Post-mortems of performance incidents usually include the plan of the culprit query. Linking to depesz makes the post-mortem much more readable 6 months later.

Traps and common mistakes

Mistake 1 (privacy): pasting plans with sensitive data into online tools

Symptom: "I pasted my production DB's plan into depesz, now there's company info exposed."

Why it happens: depesz stores plans on its server with a public URL. Anyone with the link sees them. If the plan contains sensitive table/column names/comments, it's a leak.

How to fix it:

• Anonymize before uploading (depesz has a checkbox).
• Self-host depesz (open source).
• Use dalibo in "don't store" mode (it processes in the browser).

Mistake 2 (operational): not including BUFFERS or VERBOSE

Symptom: "I uploaded the plan to dalibo and it doesn't show me buffer info."

Why it happens: because you didn't ask EXPLAIN for it. Without BUFFERS, the tools don't invent the data.

How to fix it: always EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query> before uploading to any tool. (For JSON, add FORMAT JSON.)

Mistake 3 (interpretation): blindly trusting pgMustard's "verdict"

Symptom: "pgMustard says 'add a covering index', I add it."

Why it's sometimes wrong: the tools detect patterns, but they don't understand context. A covering index can be correct in isolation, but bad if the table has 90% writes (each extra index penalizes inserts/updates). The tools don't know your workload mix.

How to fix it: use them as hypothesis input, not as a prescription. "pgMustard suggests X" is one of several data points. Validate with EXPLAIN ANALYZE before and after.

Mistake 4 (workflow): the tool before basic reading

Symptom: "Without pasting the plan into depesz I can't read anything."

Why it's sometimes wrong: short plans (5-10 lines) are perfectly readable in text. If you always depend on the tool, you don't internalize the concepts. And for quick ad-hoc analysis, the round trip to the tool is overhead.

How to fix it: train yourself to read plans of 5-15 lines in text. For large plans, a tool. The base skill is reading plans, not using a UI.

Mistake 5 (subtle): comparing before/after plans with different runs

Symptom: "I pasted the before and after plans into depesz, the after one says 200ms instead of 100ms, did it get worse?"

Why it's sometimes wrong: different runs can have different cache states (cold vs warm), other system load, etc. The "time" in an individual run is noisy.

How to fix it: to compare, do multiple runs of each version, compute the median. depesz/dalibo don't do this averaging for you. More in module 1 (benchmarking).

Exercises

Exercise 1: your first link in depesz

Capture the plan of a query from your database with EXPLAIN (ANALYZE, BUFFERS, VERBOSE) <query>. Paste it into depesz. Note: the most expensive node, the highest row mismatch ratio, and the inclusiveness vs exclusiveness of the root node.

Exercise 2: paste JSON into dalibo

Capture the same plan but with FORMAT JSON. Paste it into explain.dalibo.com. Compare the visual representation with depesz's. On what type of plan does dalibo help you most?

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON) <query>;

Exercise 3: create a large plan on purpose

Design a query that generates a complex plan (>30 lines), run it with EXPLAIN (ANALYZE, BUFFERS, VERBOSE), and identify: which tool you find most useful for analyzing it and why.

-- Assumes tables books, authors, reviews
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
WITH active_authors AS (
  SELECT a.id, a.name, COUNT(b.id) as book_count
  FROM authors a
  LEFT JOIN books b ON b.author_id = a.id
  GROUP BY a.id, a.name
  HAVING COUNT(b.id) > 5
),
recent_reviews AS (
  SELECT r.book_id, AVG(r.rating) as avg_rating, COUNT(*) as review_count
  FROM reviews r
  WHERE r.created_at > '2024-01-01'
  GROUP BY r.book_id
)
SELECT b.title, aa.name as author, rr.avg_rating, rr.review_count
FROM books b
JOIN active_authors aa ON aa.id = b.author_id
LEFT JOIN recent_reviews rr ON rr.book_id = b.id
WHERE b.published_year > 2000
ORDER BY rr.avg_rating DESC NULLS LAST
LIMIT 50;

Exercise 4: anonymization

Take a plan that has table/column names you wouldn't want to share. Upload it to depesz with anonymization enabled. Compare the anonymized output with the original. How robust is the anonymization?

Exercise 5: compare before/after plans

Capture the plan of a query with a missing index. Apply the index. Recapture the plan. Upload both to depesz, compare side by side in separate tabs. How does it change? Which metrics improve?

DROP INDEX IF EXISTS idx_books_author;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM books WHERE author_id = 42;

Seq Scan on books  (cost=0.00..1870.00 rows=22 width=22) (actual time=0.024..7.812 rows=22 loops=1)
  Filter: (author_id = 42)
  Rows Removed by Filter: 99978
  Buffers: shared hit=810
Execution Time: 7.834 ms

CREATE INDEX idx_books_author ON books(author_id);
ANALYZE books;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM books WHERE author_id = 42;

Index Scan using idx_books_author on books  (cost=0.42..47.21 rows=22 width=22) (actual time=0.045..0.123 rows=22 loops=1)
  Index Cond: (author_id = 42)
  Buffers: shared hit=4
Execution Time: 0.145 ms

Exercise 6: team workflow

Imagine you're part of a team and a colleague pings you: "this query is slow, can you help me?". Design the ideal message workflow (what you ask them for, what you reply, in what order) using these tools.

Summary and next step

In this capsule you learned:

• Plans <15 lines: plain text. Plans >15 lines: a visual tool.
• explain.depesz.com is the PostgreSQL community's standard tool. Colored table, permanent link, optional anonymization.
• explain.dalibo.com is better for plans with many nesting levels or parallel ones. 2D tree view.
• pgMustard automates the analysis with specific suggestions — useful for teams that don't have an in-house DBA, paid but powerful.
• Capture with JSON (FORMAT JSON) for tools that support it — more data than text.
• Privacy matters: plans with sensitive data → anonymization + dalibo browser-only or self-hosted depesz.
• Standard team workflow: capture the plan, upload to a tool, share a link, diagnose together on the same visual representation.

Before moving on you should be able to:

• Upload a plan to depesz and read the colored table
• Upload a plan in JSON to dalibo and navigate the tree
• Decide which tool to use based on the type of plan
• Anonymize plans before sharing sensitive info

Next capsule — Project: diagnosing the Bookstore. It's the module's finale. You'll take 6 real query plans from the module 1 baseline, capture them with the complete incantation, analyze them one by one using everything you learned (capsules 02-07), and produce a PLANS.md that documents the diagnosis of each one with root-cause hypotheses and a mapping to which next module will resolve it. It's the bridge between "you measured what" (module 1) and "you're going to optimize" (modules 3-8).

Resources

1. explain.depesz.com — the tool. Hubert "depesz" Lubaczewski.
2. explain.depesz.com source code (GitLab) — for self-hosting.
3. explain.dalibo.com — Dalibo's tool.
4. pev2 (dalibo source code) — the JavaScript library that renders dalibo. Open source.
5. pgMustard — a paid tool with automated analysis.
6. Hubert "depesz" Lubaczewski blog — the blog of depesz's author. Multiple articles on reading plans.
7. PostgreSQL Wiki — Plan visualization tools — the official list of tools maintained by the community.
8. Lukas Eder — "EXPLAIN visualization tools comparison" — an independent comparison of tools (includes a non-PG perspective).

Module 2 — Database Performance & Query Tuning Guide