Re: For index bloat: VACUUM ANALYZE vs REINDEX/CLUSTER

On 19/09/2007, Gregory Williamson <Gregory.Williamson@digitalglobe.com> wrote:

...

>  Can't speak directly to PostgreSQL but in Informix the fill factor is
> useful for tweaking indexes. A very high fill factor is useful for tables
> that are static -- any inserts or changes to the index trigger a *lot* of
> moving of b-tree branches. But the high fill factor means that each page has
> more useful data references in it. A very low fill factor means that pages
> are "sparse" and so inserts and updates are less likely to trigger massive
> b-tree rebalancings.



Assuming pgsql's fill factor is similar to Informix' (yes, a highly
suspect assumption), could we say:

1. A small fill factor such as 10 or 20 would be good for the index
size and will not trigger massive btree rebalancings? (I'm first
playing with a value of 60 for now and seeing how it works out...seems
ok at the moment!)

2. Is this fill factor enough to have on the table, or should I also
do a fill factor for specific indexes? Or both the table and the
index? (I have four btree indexes on the table)

Thanks