I'll be showcasing some exciting PostgreSQL work, featuring contributions from Microsoft engineers at pgconf.eu: Postgres 19, 20, & Beyond: Live Demos of New Features & Tools. During the demos, we'll explore many execution plans, including a new PostgreSQL 19 feature—the IO option for EXPLAIN.
EXPLAIN (ANALYZE) executes the query and reports runtime statistics. BUFFERS shows logical buffer activity: cache hits and reads. IO goes further, reporting how the read stream behaved: how far ahead PostgreSQL was able to prefetch, how many physical I/O requests were issued, their sizes, the level of concurrency, and how often the consumer had to wait.
For a long time, PostgreSQL relied primarily on the operating system and filesystem for read-ahead. PostgreSQL 17 introduced read streams, giving the executor its own streaming read-ahead mechanism. PostgreSQL 18 added asynchronous I/O infrastructure, including io_method implementations such as worker-based AIO and Linux io_uring. PostgreSQL 19 exposes this activity in EXPLAIN (ANALYZE, IO).
When PostgreSQL knows it will read multiple table blocks, for example during a Seq Scan, Bitmap Heap Scan, or Tid Range Scan, it can use a read stream. The stream looks ahead, combines nearby blocks into larger I/O requests, and keeps buffers pinned ahead of the consumer. That distance is reported in the Prefetch line.
Every time a buffer is handed to the scan node, whether it was already cached or had to be read from storage, PostgreSQL samples the current prefetch depth. This is why the Prefetch numbers are scoped to buffer consumption, not just to physical reads.
Physical I/O requests are reported separately on the I/O line. PostgreSQL records the number of I/O requests issued, the average number of blocks read per request, the number of other I/Os already in progress when a request was submitted, and how often the consumer encountered an I/O that had not yet completed.
Here is an example:
postgres=# explain (analyze, buffers, IO, verbose off, costs off)
postgres-# select * from demo
;
QUERY PLAN
------------------------------------------------------------------------
Seq Scan on demo (actual time=0.703..1096.397 rows=1000000.00 loops=1)
Prefetch: avg=34.56 max=68 capacity=71
I/O: count=1825 waits=7 size=15.97 in-progress=3.70
Buffers: shared hit=16310 read=29145
Planning Time: 0.068 ms
Execution Time: 1924.887 ms
(6 rows)
The scan touched 16310 + 29145 = 45,455 shared buffers. With the default 8 KB block size, that is about 355 MiB of table data. Of those buffers, 16310 were already in shared buffers, and 29145 had to be read.
The Prefetch line indicates how far ahead the read stream was able to stay:
-
capacity=71is the maximum number of buffers this stream was allowed to pin ahead of the consumer. -
max=68indicates the stream reached a peak depth of 68 pinned buffers, close to the limit. -
avg=34.56indicates that, across all 45,455 buffer hand-offs to the scan node, the stream had about 35 buffers pinned ahead on average.
The I/O line reports the physical reads:
-
count=1825indicates that PostgreSQL issued 1,825 distinct I/O requests. -
size=15.97indicates that each request read about 16 blocks on average:29145 / 1825. -
in-progress=3.70indicates that, when a new I/O was submitted, about 3.7 other I/Os were already in progress on average. -
waits=7indicates that only 7 of the 1,825 I/O requests had not completed by the time the consumer reached their first buffer.
That last point is important: waits means that, of the 1,825 I/O requests, only 7 were still unfinished when the scan needed them. The other 1,818 completed early enough that prefetching and asynchronous execution hid their latency.
Let's analyze these further. Little's Law states that the average number of items in a stable system (L) equals the product of the average arrival rate (λ) and the average time each item spends in the system (W).
Two counters are directly reported: in-progress=3.70, indicating the average number of concurrent I/O requests (L), and count=1825, representing completed requests over the total scan time of 1096.397 ms. Assuming I/O requests were issued and completed steadily during this period, the request completion rate λ = count / time = 1825 / 1.096397 s equals approximately 1664.5 requests per second. Applying Little's Law (L = λ · W), we find the average time a request spends in the system from submission to completion as W = L / λ = 3.70 / 1664.5, which is roughly 0.002223 seconds or 2.22 milliseconds.
This is a derived average, not an actual reported value. Using it as a per-request estimate, the reported waits=7—the number of requests the consumer reached before completing—sets an upper limit on total blocked time of 7 × 2.22 ms = 15.56 ms. This represents approximately 15.56 / 1096.397 = 1.42% of the scan's total elapsed time. Keep in mind, this is an upper bound, not an exact measurement, because waits counts events rather than durations. A wait only adds to the remaining latency of a request already in progress, which is at most its average latency of 2.22 ms.
Don't mistake this 1.42% for the total I/O. It's a Seq Scan with most of the work involving I/O, but this isn't visible in the foreground process because most I/O occurred in the background through a read stream that held about 35 buffers pinned ahead on average (avg=34.56). Multiple requests were usually active at once (in-progress=3.70). The I/O operations and row processing largely overlapped for nearly the entire 1096.397 ms. The 1.42% is an upper estimate—based on waits=7 and average I/O latency, not a direct measurement—representing the brief moments when the overlap broke: when the scan ran out of its prefetched buffers and had to pause for a specific request to finish.
In one sentence, this EXPLAIN output tells us that PostgreSQL kept roughly 35 buffers prefetched ahead of the sequential scan, combined 29,145 block reads into 1,825 larger I/O requests of about 16 blocks each, maintained about four concurrent reads on average, and stalled for at most ~1.42% of the scan's time waiting on the 7 I/O requests that weren't ready in time.
