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 <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend