Re: TODO-Item: B-tree fillfactor control

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@2ndquadrant.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.


> 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%


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


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

---
ITAGAKI Takahiro
NTT Cyber Space Laboratories