Re: when to reindex?

Roberts, Jon wrote:

> Based on this, I have the fillfactor set lower than the default 90 but
> this will fill up and it will run slower over time. I want to automate
> the reindex process but only reindex when needed. I have a pretty large
> database so I can't reindex everything regardless if it needs it or not.

I'll try to offer a few pointers, but be aware that there might be a
MUCH better way of doing things that I'm just not aware of. I'm lucky
enough not to need to worry too much about either index bloat or
occasional index rebuilds.

Anyway, the following query:

select relname, relpages from pg_class where relkind = 'i';

will get you a list of your indexes and the associated page counts.
Unfortunately it gives you no indication of the actual use of those pages.

You might be able to put something together using the `pageinspect'
module. For example, this query:

select (stats).* FROM
(select
pageno,
bt_page_stats('INDEXNAME', pageno)
AS stats
from generate_series(
1,
(select relpages from pg_class
where relname = 'INDEXNAME')
- 1
) as pageno
) AS x;

will return details about each page.

Using some simple aggregates should then let you get some idea of the
space use in the index. For example, replacing the:

SELECT (stats).* FROM

line in the above query with:

SELECT
SUM((x).free_size) AS totalfree,
SUM((x).page_size) AS totalsize,
SUM((x).free_size)::numeric / SUM((x).page_size)::numeric
* 100 AS freepercent
FROM -- ... rest of prior query ...

should return the percentage of free space in the index. This *might* be
a good metric for whether a reindex is appropriate. You can probably
obtain the configured fill factor on the index from the catalogs
somewhere (I don't know how off the top of my head) and compare that to
the free space to see if it's excessive.

I'm not at all sure that this is correct, so please point out if I've
made some incorrect assumption or just misunderstood something.

By the way, I also just run into this message:
http://unixadmintalk.com/f48/measuring-table-index-bloat-352483/
when doing a quick search. It might be useful.

--
Craig Ringer