Build1 publisher2 min readPublished
Postgres 19's EXPLAIN IO line reports 29,145 block reads as 1,825 physical requests
The new IO option reports how far the read stream stayed ahead, how many requests it issued and how large they were, and in the demo plan only 7 of 1,825 requests arrived too late for the scan that needed them.
The Engineer · Build desk
What happened
- PostgreSQL 19 adds an IO option to EXPLAIN that reports how far ahead the read stream prefetched, how many physical I/O requests it issued, their sizes, the level of concurrency, and how often the consumer waited.
- Read streams gave the executor its own streaming read-ahead in PostgreSQL 17, and PostgreSQL 18 added the asynchronous I/O infrastructure, with io_method implementations including worker-based AIO and Linux io_uring.
- A read stream can be used when PostgreSQL knows it will read multiple table blocks, as in a Seq Scan, Bitmap Heap Scan or Tid Range Scan, combining nearby blocks into larger requests and pinning buffers ahead.
- In the demo plan, a sequential scan prints I/O count=1825 waits=7 size=15.97 in-progress=3.70 beside Buffers shared hit=16310 read=29145, with the node's actual time reaching 1096.397 ms.
- The same plan's Prefetch line reads avg=34.56 max=68 capacity=71, where capacity is the most buffers the stream was allowed to pin ahead of the consumer and max the deepest it actually got.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- capability A slow scan can be split into time spent waiting on storage and time spent moving blocks. The waits counter says how many requests the scan had to sit on.
- decision Reported peak depth against allowed depth gives a test for the prefetch limit before anyone raises it. Max well under capacity means the stream never got near the ceiling it had.
- constraint The post names only Seq Scan, Bitmap Heap Scan and Tid Range Scan as read-stream users, and says nothing about index-driven plans.
The size counter on the I/O line is blocks per request, and the post derives it in one division: 29,145 blocks read over 1,825 requests gives 15.97 [9]. At the default 8 KB block that is about 128 KiB per request [3]. Those 29,145 blocks are roughly 228 MiB of physical read [1], and the Seq Scan node's actual time ran to 1096.397 ms [7], so the stream sustained about 208 MiB/s [2].
"waits means that, of the 1,825 I/O requests, only 7 were still unfinished when the scan needed them," the post's author wrote [10]. Seven out of 1,825 is 0.38% [4]. Prefetching and asynchronous execution hid the latency of the other 1,818, according to the post [11].
Two of the counters then feed Little's Law. Average concurrency is 3.70 other I/Os in flight at submission time [16], and the completion rate is 1,825 requests over 1.096397 s, about 1664.5 per second, which puts mean time in the system at 3.70/1664.5, roughly 0.002223 [12], or about 2.2 ms a request [5]. The post names its own condition, "Assuming I/O requests were issued and completed steadily during this period" [13]. Averaged across 1.1 seconds, a cluster of slow reads at either end of the scan does not show up in that mean.
The Prefetch line needs the same care. Depth is sampled at every buffer hand-off, cached or not [5], and 16,310 of the 45,455 hand-offs were cache hits, 36% of them [6]. A fully cached scan will still report a prefetch average. That is an odd number to tune against. On the same line, max=68 against capacity=71 means the stream got within three buffers of the limit it was allowed to pin ahead [6][7].
The plan comes from a preview of a pgconf.eu session, "Postgres 19, 20, & Beyond: Live Demos of New Features & Tools", with contributions from Microsoft engineers [15]. The post does not state the storage or the io_method behind it [14]. For the 208 MiB/s or the 2.2 ms to say anything about another system, block size, device and io_method would all have to match.
What to watch
- Whether nodes that do not use a read stream ever get an I/O line.
- Whether the counter names (avg, max, capacity, count, waits, size, in-progress) survive to the PostgreSQL 19 release notes.
- A published plan with a stated io_method and device, which would let the 2.2 ms mean be checked against the hardware's own latency numbers.