Jeff Frost <jeff@frostconsultingllc.com> 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