I'd like to know how many pages (table + index, if any) are read from disk (and not from cache) when running a single Postgres query. Even better, if there is any way to extract this info from an EXPLAIN ANALYZE.
That information is available when you add the buffers option: explain (analyze, buffers) select ...
e.g.
explain (analyze, buffers)
select *
from salaries s
join employees e on e.emp_no = s.emp_no
where s.emp_no in ('10001', '20001', '30001', '40001', '50001', '99999', '99996');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.85..1016.67 rows=81 width=51) (actual time=0.152..18.530 rows=96 loops=1)
Buffers: shared hit=407 read=5
I/O Timings: read=15.340
-> Index Scan using salaries_pkey on salaries s (cost=0.43..349.03 rows=81 width=20) (actual time=0.082..0.332 rows=96 loops=1)
Index Cond: ((emp_no)::text = ANY ('{10001,20001,30001,40001,50001,99999,99996}'::text[]))
Buffers: shared hit=28
-> Index Scan using employees_pkey on employees e (cost=0.42..8.24 rows=1 width=31) (actual time=0.187..0.187 rows=1 loops=96)
Index Cond: ((emp_no)::text = (s.emp_no)::text)
Buffers: shared hit=379 read=5
I/O Timings: read=15.340
Planning Time: 256.640 ms
Execution Time: 18.628 ms
You can see that a total of 412 pages (=blocks) were needed. 5 of them had to be fetched from the file system ("read=5") - those 5 were needed because of the Index Scan on employees_pkey