Thread: Index utilization

I am working on cleaning up a bloated database.  I have been
reindexing etc.  There appear to be a good number of never to almost
never used indexes.  I am looking in pg_stat_user_indexes which yields
some questions.

Assuming that the reset stats on server is not turned on how old are
stats?

Are there any good open source tools to monitor how often indexes are
used?

I was basically thinking of writing end of day numbers for the stats
in a table so I can plot index usage over a few months to see what is
getting used.  Does anybody see a problem with that?

Thanks in advance

2009/8/13  <kbarnard@kevinbarnard.net>:
> I am working on cleaning up a bloated database.  I have been reindexing etc.
>  There appear to be a good number of never to almost never used indexes.  I
> am looking in pg_stat_user_indexes which yields some questions.
>
> Assuming that the reset stats on server is not turned on how old are stats?
>

Old as your first start of the server. Except if you made  a
total dump of the database that could erase them.

> Are there any good open source tools to monitor how often indexes are used?
>

It's too specific. But your question is interesting to analyze
this kind of features.

> I was basically thinking of writing end of day numbers for the stats in a
> table so I can plot index usage over a few months to see what is getting
> used.  Does anybody see a problem with that?
>

Could be for the firsts months, then you will not need it
any more. but sounds charm for me. If i can offer some code
let me know :)


--
              Emanuel Calvo Franco
             Database consultant at:
                    www.siu.edu.ar
        www.emanuelcalvofranco.com.ar