Re: TODO-Item: B-tree fillfactor control

On Mon, 2006-02-06 at 13:27 +0900, ITAGAKI Takahiro wrote:
> This is a draft patch for index fillfactor control discussed in
> http://archives.postgresql.org/pgsql-hackers/2006-02/msg00013.php
>
> I added the following features:
>   - Add support for btree, hash and gist.
>   - Syntax extension using PCTFREE.
>   - Save settings to catalog. Next REINDEX will use the last value.
>
> I'd like to ask index developers to review the patch, especially
> the method to control fill factor for hash and gist.
> I'll write documentations if there is no problem in the features.
> Comments are welcome.

Looks pretty complete to me. A useful patch for large databases.

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. Do we have any tests to show
whether 3*setting is the right value for b-tree node pages? It sounds
about right but I have no evidence either way.

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.

"The steady-state load factor for btrees is usually estimated at 70%."
but we recognise that estimate as being from the 1980s and not
necessarily reflecting all application types for which we now use
databases.

Can we use the PCTFREE setting to control the RIGHTMOST behaviour? If I
manually control the PCTFREE I want it to work like that all of the
time, not just some of the time.

[i.e. with this patch if I fill an index with 1000 blocks of data using
PCTFREE 0 the index will use 1000 blocks. If I COPY another 1000 blocks
of data the index would then be 1500 blocks larger, 2500 total. The
current cvstip acts thus: if I fill an index with 1000 blocks of data
the index will use 1111 blocks. If I COPY another 1000 blocks of data
the index would then be 1500 blocks larger, 2611 total. I'd like to be
able to have the index use only 2000 blocks when PCTFREE=0 - if I ask
for fully packed I want fully packed, please]

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.

Best Regards, Simon Riggs