Postgresql performance - Index page hits

Question

I run the following query to estimate the ratio of index pages read rom memory (buffer hits) to index pages read from disk

select
    t.schemaname, 
    t.relname as "Table Name", 
    io_i.indexrelname as "Index Name", 
    case when (io_i.idx_blks_hit <> 0 or io_i.idx_blks_read <> 0) then 
    round(io_i.idx_blks_hit/(io_i.idx_blks_hit::numeric + 
    io_i.idx_blks_read::numeric), 4) else null end as "Index Hit Ratio" 
from 
    pg_stat_user_tables t
    join pg_statio_user_indexes io_i on io_i.relid = t.relid 
order by "Index Hit Ratio" desc;

And I have several indexes where this rate is too low (below 0.7). Please advise what could be the possible reason and how to improve it.

score 5 · Accepted Answer · answered Oct 03 '17 at 07:14

shared_buffers is not big enough to contain all indexes, so queries hit the disk (or the file system cache, which is ok).

It could be that these indexes are not used very often, so it makes sense for PostgreSQL not to keep them cached. Check idx_scan in the pg_stat_user_indexes view to see how often an index was scanned since the last statistics reset.

If you can set shared_buffers high enough to contain the whole database, do so. Otherwise, read the manual which has some advice for setting shared_buffers.

Other than that, I would not do anything as long as performance is good and the I/O system is not overloaded. If you have problems, try to get more RAM. If that option is exhausted, get faster storage.

Thanks a lot. Your advise is very helpful, as indeed we have I/O utilization pikes of up to 100% quite often. — MaterialGirl, Oct 03 '17 at 09:29

Postgresql performance - Index page hits

1 Answers1