pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / 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>
  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 22:24  Tom Lane <tgl@sss.pgh.pa.us>
  parent: 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 22:41  Jeff Frost <jeff@frostconsultingllc.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  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