I have this query to analyze index usage in Postgres:
SELECT idx.* , idxio.idx_blks_read , idxio.idx_blks_hit FROM pg_stat_user_indexes idx JOIN pg_statio_user_indexes idxio USING (indexrelid) ORDER BY idx_scan;
I understand I need to compare "snapshots" of results, i.e. I need to execute this query and save the results, and some time later after some usage I will execute again and compare results.
But I want to know how to interpret some pattern of values from these columns
I don't want to know the meaning/definition of these columns, but what are the (most common) patterns of values will exhibit a "good", "bad" or "maybe" index.
As example of patterns, I noticed for PK indexes is common
idx_scan = idx_tup_read = idx_tup_fetch.
Other common pattern is very high
idx_tup_read (millions) and very small or even zero
Yet other pattern is high
idx_scan but zero
idx_tup_fetch (some of these with high both