1

I'm trying to assess the performance of a Postgres 10.6 instance. The first query below returns 0.88, the second one 0.77. Whence the discrepancy between these two queries for the cache hit-ratio?

SELECT sum(blks_hit) / sum(blks_hit + blks_read)
FROM pg_stat_database 
WHERE datname = 'the_current_db';

-- While connected to `the_current_db`, as above.
SELECT sum(heap_blks_hit) / sum(heap_blks_hit + heap_blks_read)
FROM pg_statio_all_tables;

For pg_stat_database, the docs say:

blks_read bigint

Number of disk blocks read in this database

blks_hit bigint

Number of times disk blocks were found already in the buffer cache, so that a read was not necessary (this only includes hits in the PostgreSQL buffer cache, not the operating system's file system cache)

While the docs for pg_statio_all_tables:

heap_blks_read bigint

Number of disk blocks read from this table

heap_blks_hit bigint

Number of buffer hits in this table

(All the documentation links are for version 13, as it has more granular intra-page linking than version 10.)

VanillaDonuts
  • 135
  • 7
  • pg_statio_all_tables shows only data for the current tables, not the ones that are dropped. – Frank Heikens Mar 28 '22 at 14:11
  • Would then `pg_statio_all_tables` be preferred source of measurements, when assessing "live" performance? And would the above queries return the same after a `VACUUM FULL`? – VanillaDonuts Mar 28 '22 at 14:31
  • Also, where is this documented, that `pg_stat_database` also includes dropped tables (and `pg_statio_all_tables` not)? – VanillaDonuts Mar 28 '22 at 15:50

0 Answers0