How many disk pages are read by a query in Postgres?

Question

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.

score 5 · Accepted Answer · answered Dec 13 '20 at 13:19

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

But that doesn't distinguish reads from disk from reads from OS/FS cache. It only separates out shared_buffers cache. — jjanes, Dec 13 '20 at 13:20
I assumed "are read from disk (and not from cache)" refers to the Postgres cache. I don't see a the filesystem cache mentioned in the question. But it won't be possible to obtain that information from the execution plan (and I doubt it's easily possible without using some low level debug tools for the operating system) — , Dec 13 '20 at 13:22

jjanes · Answer 2 · 2020-12-13T13:40:02.153

There is an extension that is supposed to separate true disk reads from FS cache reads, but it appears to only give data in aggregate, like pg_stat_statements does, and not in individual executions like EXPLAIN (ANALYZE, BUFFERS) does.

You can also use set log_executor_stats TO on;, perhaps combined with set client_min_messages TO log; to get top-level actual disk reads for each execution. The user experience here is pretty gross, though.

How many disk pages are read by a query in Postgres?

2 Answers2