1

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

  • idx_scan
  • idx_tup_read
  • idx_tup_fetch
  • idx_blks_read
  • idx_blks_hit

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 idx_tup_fetch. Yet other pattern is high idx_scan but zero idx_tup_read and idx_tup_fetch (some of these with high both idx_blks_read and idx_blks_hit).

Improve this question
1
  • Those tables are almost entirely useless. You should identify problems and then work to solve them, not identify imaginary solutions and then seek out problems they solve.
    – jjanes
    Jul 7, 2021 at 15:48

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.