Re: TODO-Item: B-tree fillfactor control
От | Simon Riggs |
---|---|
Тема | Re: TODO-Item: B-tree fillfactor control |
Дата | |
Msg-id | 1139348667.1258.163.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | TODO-Item: B-tree fillfactor control (ITAGAKI Takahiro <itagaki.takahiro@lab.ntt.co.jp>) |
Ответы |
Re: TODO-Item: B-tree fillfactor control
Re: TODO-Item: B-tree fillfactor control |
Список | pgsql-patches |
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
В списке pgsql-patches по дате отправления: