PostgreSQL Administration - pg_stat_user_indexes view clarification

Author

pg_stat_user_indexes view clarification

Jeff Frost

2006-01-30, 8:24 pm

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,i
ndexrelname,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,i
ndexrelname,idx_tup_
read,idx_tup_fetch from
pg_stat_user_indexes
where idx_tup_read != idx_tup_fetch;

--
Jeff Frost, Owner < jeff@frostconsulting
llc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2006-01-30, 8:24 pm

Jeff Frost < jeff@frostconsulting
llc.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,i
ndexrelname,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,i
ndexrelname,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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Jeff Frost

2006-01-30, 8:24 pm

Thanks Tom! More questions inline below:

On Mon, 30 Jan 2006, Tom Lane wrote:

>
>
> 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@frostconsulting
llc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links