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.
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.