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

On 19/09/2007, Richard Broersma Jr <rabroersma(at)yahoo(dot)com> wrote:
> --- Phoenix Kiula <phoenix(dot)kiula(at)gmail(dot)com> wrote:
>
> > 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)
>
> I don't think that fill factor can be applied to the table. The CREATE TABLE reference doc show
> that fill factor can be used in the CREATE TABLE statement, but it is only applied to syntax that
> creates an implied index.
>
> i.e. CREATE TABLE test (
> test_id INTEGER PRIMARY KEY WITH ( FILLFACTOR = 70 ),
> test_val TEXT );
>
> Primary key will create an implied index. Fill factor is applied to that implied index.
>
> Regards,
> Richard Broersma Jr.
>

Then I am confused again about how the fill factor stuff works. Let's
say I have a table with four BTREE indexes. Should all of them have a
fill factor of about 60 (lower than the default 90, that is) to be
effective? Or will it help if I lower the fill factor on only a couple
of the most often used ones? The primary key index is very, very
rarely updated so I don't need it to have a fill factor.

I could try and see these one by one, but that's no better than
touching/feeling the database blind-folded. I would love to see some
writeup about this whole thing, but it seems hard to come by!

Many thanks