Reading a 140-line indented text dump from EXPLAIN ANALYZE in psql works fine for a two-table join—and falls apart the moment a production report query nests six CTEs, three Hash Join nodes, and a correlated SubPlan.

Worse, pasting proprietary SQL execution plans—complete with internal table names, partition schemas, and filter predicates (WHERE tenant_id = '...')—into third-party cloud explainers that store plans in a remote database often violates strict SOC2 / internal security policies.

Here is a practical breakdown of the four hidden bottlenecks inside EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON), how to calculate true exclusive node time, and how to inspect plans visually in a 100% client-side flamegraph with zero server uploads.

1. Always Run (ANALYZE, BUFFERS, FORMAT JSON)

When diagnosing slow queries in staging or replicas, plain EXPLAIN ANALYZE leaves out half the story: I/O pressure. Adding BUFFERS and FORMAT JSON exposes exact page hits, disk reads, and temp spills in a machine-parseable tree:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT o.id, o.created_at, SUM(i.price_cents * i.qty) AS total_cents FROM orders o JOIN order_items i ON i.order_id = o.id WHERE o.tenant_id = 'acme_corp' AND o.status = 'PAID' AND o.created_at >= NOW() - INTERVAL '30 days' GROUP BY o.id, o.created_at ORDER BY total_cents DESC LIMIT 50;

2. Inclusive vs. Exclusive Node Time (Why Parent Nodes Look Slow)

In PostgreSQL's JSON plan output, every node reports "Actual Total Time" per loop, and includes the time spent in all of its child Plans:

$$\text{InclusiveMs(node)} = \text{Actual Total Time} \times \text{Actual Loops}$$

$$\text{ExclusiveMs(node)} = \max\left(0,\; \text{InclusiveMs(node)} - \sum_{c \in \text{children}} \text{InclusiveMs}(c)\right)$$

If you only glance at "Actual Total Time", the top-level Limit or Nested Loop always looks like the culprit—even when 94% of actual wall-clock execution happened inside a leaf Bitmap Heap Scan executed Actual Loops = 1,240 times!

Multiplying by Actual Loops first and subtracting child inclusive times immediately isolates the exact leaf or join operator burning CPU or waiting on disk.

3. Three Silent Killers to Flag Automatically

① High "Rows Removed by Filter" on Index/Heap Scans 

"Node Type": "Index Scan", "Index Cond": "(tenant_id = 'acme_corp'::text)", "Filter": "((status = 'PAID'::text) AND (created_at >= ...))", "Rows Removed by Filter": 418920, "Actual Rows": 310

The query uses an index, so developers assume it's indexed properly. In reality, Postgres fetched 419,230 heap tuples just to throw away 99.92% of them in memory. Covering the predicate with a composite or partial index (CREATE INDEX ON orders (tenant_id, created_at DESC) WHERE status = 'PAID') collapses those heap reads immediately.

② Disk Spills (Temp Written Blocks > 0)

When a Sort or Hash node exceeds your session's work_mem (default 4MB!), Postgres spills intermediate batches to disk:

  • Look for "Sort Space Type": "Disk" or "Temp Written Blocks": 12480 (each block is 8KB, so 12,480 blocks = ~97.5 MB written to disk mid-query).
  • Raising SET LOCAL work_mem = '64MB' inside the transaction turns an external merge disk sort into an in-memory quicksort (5–10x faster).

③ Bad Row-Estimate Drift (Plan Rows vs. Actual Rows)

When "Plan Rows": 12 turns out to be "Actual Rows": 84,500, the planner picks a Nested Loop instead of a Hash Join, turning an $O(N + M)$ join into $O(N \times M)$ random index lookups. Usually caused by stale table statistics (ANALYZE table_name) or correlated multi-column predicates needing CREATE STATISTICS.

4. Visualizing Query Plans 100% In-Browser (Zero Server Upload)

To inspect sensitive production EXPLAIN JSON plans without sending schema names or query literals over the network, I built an interactive client-side analyzer as part of NerdKit:

  • PostgreSQL EXPLAIN ANALYZE Flamegraph & Bottleneck Visualizer
    • Computes Exclusive vs. Inclusive execution time across Actual Loops
    • Renders an interactive Flamegraph + node breakdown table
    • Flags Filter Drop ratios, Planner Row-Estimate drift, and Shared Read / Temp Block spills automatically
    • Runs 100% locally in browser memory (verify in your Network tab: 0 outbound payload requests)

If you work heavily with Postgres schemas and TypeScript ORMs, you might also find SQL DDL to Drizzle ORM Schema Converter and the In-Memory Parquet & SQLite Viewer useful—all built on the same zero-upload client-side principle at global-utils.com.

What's the worst Actual Loops or Rows Removed by Filter surprise you've caught in a production EXPLAIN plan? Let me know in the comments!