pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pg_stat_user_indexes view clarification 3+ messages / 2 participants [nested] [flat]
* pg_stat_user_indexes view clarification @ 2006-01-30 21:22 Jeff Frost <jeff@frostconsultingllc.com> 2006-01-30 22:24 ` Re: pg_stat_user_indexes view clarification Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 3+ messages in thread From: Jeff Frost @ 2006-01-30 21:22 UTC (permalink / raw) To: 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@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_stat_user_indexes view clarification 2006-01-30 21:22 pg_stat_user_indexes view clarification Jeff Frost <jeff@frostconsultingllc.com> @ 2006-01-30 22:24 ` Tom Lane <tgl@sss.pgh.pa.us> 2006-01-30 22:41 ` Re: pg_stat_user_indexes view clarification Jeff Frost <jeff@frostconsultingllc.com> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2006-01-30 22:24 UTC (permalink / raw) To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-admin 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 ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: pg_stat_user_indexes view clarification 2006-01-30 21:22 pg_stat_user_indexes view clarification Jeff Frost <jeff@frostconsultingllc.com> 2006-01-30 22:24 ` Re: pg_stat_user_indexes view clarification Tom Lane <tgl@sss.pgh.pa.us> @ 2006-01-30 22:41 ` Jeff Frost <jeff@frostconsultingllc.com> 0 siblings, 0 replies; 3+ messages in thread From: Jeff Frost @ 2006-01-30 22:41 UTC (permalink / raw) To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin 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@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-01-30 22:41 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2006-01-30 21:22 pg_stat_user_indexes view clarification Jeff Frost <jeff@frostconsultingllc.com> 2006-01-30 22:24 ` Tom Lane <tgl@sss.pgh.pa.us> 2006-01-30 22:41 ` Jeff Frost <jeff@frostconsultingllc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox