Re: TODO-Item: B-tree fillfactor control

On Fri, 2006-02-10 at 19:12 +0900, ITAGAKI Takahiro wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > I'm surprised that you do not use the parameter to control the RIGHTMOST
> > index block split factor for B-trees, which remains at a constant 67%.
> > The PCTFREE only seems to apply at CREATE INDEX time.
>
> Thanks for pointing out. I did not inadvertently use fillfactor on
> the rightmost page. With the revised patch, PCTFREE will be considered
> in such cases.
>
> # CREATE TABLE test (i int);
> # INSERT INTO test SELECT generate_series(1, 100000);
> # CREATE INDEX btree ON test USING btree (i) PCTFREE 0;
> # SELECT relpages from pg_class where relname ='btree';
> relpages | 249
> # INSERT INTO test SELECT generate_series(100001, 200000);
> # SELECT relpages from pg_class where relname ='btree';
> relpages | 497 <-- +99.6%
>

This additional functionality looks like it would work for b-trees. I've
not looked at this for GIST and hash indexes.

The reduction in index size should give useful performance gains on
larger, growing tables with increasing keys. We'll test that.

Best Regards, Simon Riggs