Re: TODO-Item: B-tree fillfactor control

On Fri, 2006-02-10 at 19:12 +0900, ITAGAKI Takahiro wrote:
> This is a revised patch for index fillfactor control:
> - Split MAX_PCTFREE into three for each index method.
> - B-tree indexes use their own settings when rightmost page is split.
> - Fix a bug that GUC is modified when index building is canceled.
> - Add some documentations.

> Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>
> > Do you have any performance numbers for the extreme settings? It may be
> > worth having different max limits for each of the index types, since
> > they differ so widely in algorithms.
>
> Different max limits are done.
> I worry about whether index works properly on high PCTFREE settings. I found
> hash has its own sanity checking, but I don't know other indexes have.

Thanks.

> > 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 is great.

> But default settings will change. Is this ok?
>
> | | patched |
> | now | free=10 | free=0 |
> -----------------+-----+---------+--------+-
> leaf (REINDEX) | 10 | 10 | 0 |
> leaf (RIGHTMOST) | 30 | 10 | 0 | = leaf
> node (REINDEX) | 30 | 30 | 0 | = 3*leaf

I think thats appropriate; lets see what others think.

> > If we support PCTFREE for compatibility reasons should we not also
> > support the alternative FILLFACTOR syntax also? I see no reason to
> > favour Oracle/DB2 compatability at the expense of SQLServer
> > compatibility.
>
> There are few synonyms in PostgreSQL, so I think it is better for us to
> adopt only either one. I like FILLFACTOR personally, but compatibility
> with Oracle is more important to users around me.

OK, no probs.

Reading through rest of patch now.

Best Regards, Simon Riggs