Thanks Tom! More questions inline below:
On Mon, 30 Jan 2006, Tom Lane wrote:
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.
Then if idx_scan is 0, can I assume that index is not used? Do these stats
get saved or reset across postmaster restarts?
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.)
So how might I find indexes which are bloated and might need reindexing? I
know this behavior less likely in 8.x, but the docs still indicate it is
possible to have index bloat in recent versions of postgres.
--
Jeff Frost, Owner <jeff@xxxxxxxxxxxxxxxxxxxxxx>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
