pg_stat_user_indexes view clarification

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: pg_stat_user_indexes view clarification
Date: 2006-01-30 21:22:20
Message-ID: Pine.LNX.4.64.0601301319520.14339@discord.dyndns.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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?

Also would be interested to know if these changed in a certain version of
postgresql.

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;

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;

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2006-01-30 22:24:47 Re: pg_stat_user_indexes view clarification
Previous Message Anjan Dave 2006-01-30 16:23:09 invalid multibyte character for locale