Now, let’s be honest: staring at a monolithic blob of JSON or a deeply nested tree of text from EXPLAIN output is a special kind of torture. It’s like trying to appreciate a Rembrandt by looking at it through a keyhole. You can see a sliver of the genius, but you’re missing the whole picture. This is where visualization tools come in. They are the gallery that lets you step back and see the masterpiece—or, more often, the horrifying paint-by-numbers your query just created—in its full glory.

Why Bother with a Visualizer?

You could squint at the text output. I’m not going to stop you. But a good visualizer does three things text never will:

  1. It reveals structure instantly. The hierarchical nature of a plan, the way nodes feed into other nodes, is almost impossible to grasp quickly in text. A visualizer shows you the flow of data from the bottom up in a way your brain is hardwired to understand.
  2. It highlights the problem children. These tools use color-coding (red for “oh dear god,” orange for “concerning,” green for “fine, I guess”) to immediately draw your eye to the most expensive nodes. You don’t have to hunt for the biggest number; the tool screams “LOOK HERE, YOU IDIOT” in the most polite way possible.
  3. It surfaces hidden details. Many will show you additional context, like which columns are used for a filter or join, right beside the node, saving you from mentally correlating a dozen lines of text.

explain.depesz.com: The Quick and Dirty Swiss Army Knife

This is my go-to for a quick, ad-hoc analysis. It’s a brilliantly simple website built by Hubert “depesz” Lubaczewski. You just copy the entire EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) output from your query and paste it into the text box. Hit submit, and boom: a clean, colorized, indented plan.

Its killer feature is the “Exclusive” and “Inclusive” cost breakdown. Let’s say you see a nasty Gather node for a parallel plan. The “Exclusive” cost is just the overhead of the Gather itself. The “Inclusive” cost is the sum of the Gather and all the child nodes below it. This is the real cost of that part of the plan. Seeing a 10ms exclusive cost but a 5000ms inclusive cost on a Gather tells you the parallel workers are doing all the heavy lifting (which is good) and the Gather itself is cheap (which is also good).

Let’s see it in action. Here’s a plan for a simple query that goes off the rails:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

(You’d copy the resulting JSON output and paste it into the site).

On explain.depesz.com, you’d instantly see the deepest, most indented node—likely a Seq Scan on customers—lit up in a terrifying shade of red, with its inclusive time dominating the entire plan. The problem isn’t the join; it’s the full table scan you forgot to index. The visualizer makes this obvious in half a second.

pgBadger: The Log-Munching Behemoth

While explain.depesz.com is for the single query you’re wrestling with right now, pgBadger is for when you want to know what your entire database has been doing all week. It’s not a website; it’s an open-source Perl script that devours your PostgreSQL log files and generates a breathtakingly detailed HTML report.

You configure Postgres to log every query that takes longer than, say, 1 second (by setting log_min_duration_statement = 1000). pgBadger then parses those logs and gives you a holistic view of your query performance: the slowest queries, the most frequent queries, which ones are causing the most I/O, everything.

Its visualization of EXPLAIN plans is a side effect of its main purpose, but it’s incredibly powerful. It will aggregate all the plans for the same query (even if their parameters differ) and show you a representative plan. This is gold because it helps you spot patterns. Is the same query sometimes using an index and sometimes doing a sequential scan? pgBadger will show you both plans, letting you see the catastrophic effect of bad parameter values.

The setup is a bit more involved:

  1. Configure Postgres logging (use csvlog format for the best results).
  2. Run pgBadger periodically (e.g., via a daily cron job) to process the logs.
# A simple example run
pgbadger /var/lib/pgsql/15/data/log/postgresql-*.log -o /var/www/html/pgbadger_report.html

The resulting report is a self-contained HTML file you can open in any browser. It’s a treasure trove of insights, and its plan visualization is perfect for understanding the typical execution path of your most important (or most troublesome) queries.

The Inevitable Caveat: Garbage In, Garbage Out

These tools are brilliant, but they are not psychic. They visualize the plan you give them. If you run EXPLAIN without ANALYZE, you’re giving them the planner’s estimate, which is a work of fiction. It might be well-researched historical fiction, but it’s still fiction. You must use EXPLAIN (ANALYZE) to get the real runtime information for the visualizations to be meaningful. Also, always include BUFFERS if you can; it adds crucial information about cache efficiency that these tools will also visualize.

So, use these tools. They don’t replace understanding what a Hash Join or a Bitmap Heap Scan is, but they absolutely accelerate the process of finding out where to apply that understanding. They turn a tedious chore into a slightly less tedious—and sometimes even enjoyable—treasure hunt for your database’s inefficiencies.