Hubert 'Depesz' Lubaczewski: Per-Worker, and Global, IO Bandwidth in Explain Plans

Why It Matters

It gives DBAs actionable insight into parallel query I/O, enabling more accurate capacity planning and performance tuning. The metrics expose hidden bandwidth demands that wall‑clock time alone masks.

Key Takeaways

• New EXPLAIN output shows per-worker I/O bandwidth.
• Total bandwidth calculated from exclusive time across workers.
• Helps diagnose parallel query performance bottlenecks.
• Shows discrepancy between wall‑clock and summed I/O time.
• Enables more accurate storage subsystem sizing.

Pulse Analysis

PostgreSQL’s EXPLAIN command has long been a staple for query analysis, yet its traditional output omitted granular I/O timing for parallel workers. Without per‑worker data, administrators could only infer overall disk usage, often missing the nuance of how concurrent processes share storage bandwidth. The recent enhancement to explain.depesz.com fills this gap by integrating I/O timings directly into the plan nodes, offering a clearer picture of how each worker contributes to total I/O load.

The new display presents three key figures: total data volume, average per‑worker throughput, and exclusive‑time bandwidth required to meet the wall‑clock duration. In the cited parallel sequential scan, 39 GB were read across three workers, resulting in ~273 MB/s per worker while the disks collectively delivered ~739 MB/s to satisfy the 53‑second wall‑clock window. This distinction is crucial because summed I/O time (144 s) exceeds wall‑clock time, a phenomenon that previously confused performance diagnostics. By separating per‑worker averages from total bandwidth, the tool clarifies the true pressure placed on storage subsystems during parallel execution.

For database operations teams, these insights translate into more precise capacity planning and targeted tuning. Knowing the exact bandwidth each worker consumes helps identify whether storage is a bottleneck or if query parallelism is under‑utilized. It also aids in benchmarking hardware upgrades, as administrators can now match disk I/O capabilities to the observed exclusive‑time demands. As PostgreSQL continues to evolve its parallel processing features, such detailed metrics will become indispensable for maintaining optimal performance in data‑intensive environments.