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

> 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