i have one dockerized postgres database (The host has 1GB of ram and 2 CPU) with some tables, on a specific table, that it's used to read and write data continuously (every 5 seconds), i have strange issues, as you can see, the ratio between heap_read and heap_hit is quite low:

enter image description here

The table, has not so many data, only 300K rows, and the structure of the table it's the following:

enter image description here

Here it's the index stats:

enter image description here

I mean, the system seems to work fine...but i need to know if i have to do some specific tweaks in order to make it work better and have the heap_read/heap_hit ratio close enough to 0.99?



You are treating it as if block reads are a subset of block hits. That isn't correct, the two are mutually exclusive. To get a real hit ratio, you would have to compute as sum(hits)/sum(hits+reads). But just looking at hit ratios is pretty useless anyway, in general but especially in PostgreSQL which doesn't use DirectIO but rather sits on top of the OS's file cache. Hit ratios are part of mythology-based administration.

For all we know, this table has only had one or two seq scan done on it (for example, the ones which were done in order to create the index in the first place) and that event is now dominating your statistics. You could reset the statistics (or better yet just stop looking at them) to get rid of one-time events.

The columns seq_scan, seq_tup_read, idx_scan, idx_tup_fetch are occasionally (but rarely) useful in figuring out the source of a problem when a problem is known to exist. They are entirely useless when there is no problem to diagnose in the first place.

You should instead spend your time doing useful things, like setting up a slow query log ("log_min_duration_statement" or better yet auto_explain, and pg_stat_statements), setting up test/dev/QA environments, or running disaster recovery drills so you know know that your backups are working and that you know how to use them.

My favorite settings of auto_explain for new projects are:

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '50ms'

And then increase log_min_duration if you find it is catching too many queries.

Having log_analyze=on while also having log_timing=on imposes a pretty high overhead on all your queries; so setting log_timing=off is a compromise to get as much detail as possible but with minimal overhead.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.