Re: EXPLAIN: showing ReadStream / prefetch stats

On Tue, Apr 7, 2026 at 1:00 AM Lukas Fittl <lukas(at)fittl(dot)com> wrote:
>
> Btw, in that same test, when I re-run I get Prefetch but not I/O -- why is that?
>
> Seq Scan on organizations (cost=0.00..7.42 rows=1 width=483) (actual
> time=0.034..0.042 rows=1.00 loops=1)
> Filter: (slug = 'pganalyze'::text)
> Rows Removed by Filter: 113
> Prefetch: avg=1.00 max=1 capacity=94
> Buffers: shared hit=6
>
> (if its all buffers hit, why are there any prefetches at all?)

Here is a more self-contained reproducer:

CREATE TABLE test (id int);
INSERT INTO test SELECT * FROM generate_series(0, 100000);

-- restart server

EXPLAIN (ANALYZE, IO) SELECT * FROM test;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..1572.65 rows=112965 width=4) (actual
time=0.075..4.046 rows=100001.00 loops=1)
Prefetch: avg=22.62 max=32 capacity=94
I/O: count=31 waits=5 size=14.29 inprogress=1.77
Buffers: shared read=443
I/O Timings: shared read=0.156
Planning:
Buffers: shared hit=15 read=7
I/O Timings: shared read=0.114
Planning Time: 0.356 ms
Execution Time: 7.020 ms
(10 rows)

EXPLAIN (ANALYZE, IO) SELECT * FROM test;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..1572.65 rows=112965 width=4) (actual
time=0.027..5.520 rows=100001.00 loops=1)
Prefetch: avg=1.00 max=1 capacity=94
Buffers: shared hit=443
Planning Time: 0.094 ms
Execution Time: 9.311 ms
(5 rows)

Thanks,
Lukas

--
Lukas Fittl