Jeff Frost <jeff@xxxxxxxxxxxxxxxxxxxxxx> writes: > Can someone set me straight on whether the following statements are true in > postgresql-8.1.x and if they aren't true, what queries might I need to run to > find these answers? > The following query shows all indexes which are not used. > select schemaname,relname,indexrelname,idx_tup_read,idx_tup_fetch from > pg_stat_user_indexes where idx_tup_read = 0; It's probably more reliable to look at whether idx_scan is increasing, as idx_tup_read wouldn't increment during a scan that found zero matching rows. > The following query shows all indexes which have differing values between > idx_tup_read and idx_tup_fetch indicating indexes which likely need rebuilt > via REINDEX: > select schemaname,relname,indexrelname,idx_tup_read,idx_tup_fetch from > pg_stat_user_indexes where idx_tup_read != idx_tup_fetch; Uh, no, that does NOT imply a need for REINDEX. In particular, a bitmap indexscan increments idx_tup_read but not idx_tup_fetch --- the heap fetches are counted in the parent table's idx_tup_fetch counter instead. (This is because, in the situation where we are ANDing or ORing multiple indexes in a bitmap scan, assigning responsibility for a heap fetch to any particular index is impractical and likely misleading anyway.) I believe the details of the distinction between idx_tup_read and idx_tup_fetch changed in 8.1, but I don't remember exactly how it worked before. regards, tom lane
