Re: EXPLAIN: showing ReadStream / prefetch stats

On Tue, Apr 7, 2026 at 1:00 AM Lukas Fittl <lukas@fittl.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