Re: [HACKERS] A couple thoughts about btree fillfactor
On Mon, Jul 10, 2006 at 12:36:34PM -0400, Tom Lane wrote: Now that the index options infrastructure is in, I am having a couple of second thoughts about the specific behavior that's been implemented, particularly for btree fillfactor. 1. ... I'm thinking we could change the nbtsort.c code to work like stop filling page when fillfactor is exceeded AND there are at least two entries already. Then any old fillfactor would work. 2. ... There's a case to be made for making leaf and non-leaf fillfactors accessible as separate knobs, but I'm inclined just to use a fixed value of 70 for non-leaf factor 3. What should the minimum fillfactor be? The patch as submitted set the minimum to 50% for all relation types. I'm inclined to think we should allow much lower fillfactors, maybe down to 10%. A really low fillfactor could be a good idea in a heavily updated table --- at least, I don't think we have any evidence to prove that it's not sane to want a fillfactor below 50%. Comments? regards, tom lane I would like to place my vote for supporting fillfactors less than 50%. Like you mentioned, a heavily updated table could be forced to a page split before VACUUM freed the items and made them available for reuse. I also think that points 1 and 2 are reasonable. Ken ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] A couple thoughts about btree fillfactor
Now that the index options infrastructure is in, I am having a couple of second thoughts about the specific behavior that's been implemented, particularly for btree fillfactor. 1. The btree build code (nbtsort.c) is dependent on the assumption that the fillfactor is at least 2/3rds. This is because we must put at least two keys in each page, and with maximally sized keys (1/3rd page) it might try to put only 0 or 1 tuple in a page if fillfactor is small. However, maximally sized keys are certainly a corner case, and in more usual situations a smaller fillfactor could be useful. I'm thinking we could change the nbtsort.c code to work like stop filling page when fillfactor is exceeded AND there are at least two entries already. Then any old fillfactor would work. 2. The build code is also set to force fillfactor 70 on non-leaf pages, using the user-specified fillfactor only on leaf pages. I think this is reasonable: if you're using a small fillfactor to avoid leaf page splits, then there shouldn't be much need for new insertions on upper pages, hence not much need for extra free space there; and having a low fillfactor on upper pages will force the tree to be much deeper and hence more expensive to search. In the other case (leaf fillfactor higher than 70, indicating index is expected to be static), I'm still not inclined to use the user fillfactor for non-leaf pages, because if a split does occur it will be very expensive if we have to propagate splits all the way up the tree. There's a case to be made for making leaf and non-leaf fillfactors accessible as separate knobs, but I'm inclined just to use a fixed value of 70 for non-leaf factor. The index page split code is currently getting this wrong either way (it's applying the user fillfactor to rightmost pages on all tree levels). 3. What should the minimum fillfactor be? The patch as submitted set the minimum to 50% for all relation types. I'm inclined to think we should allow much lower fillfactors, maybe down to 10%. A really low fillfactor could be a good idea in a heavily updated table --- at least, I don't think we have any evidence to prove that it's not sane to want a fillfactor below 50%. Comments? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] A couple thoughts about btree fillfactor
On Mon, Jul 10, 2006 at 12:36:34PM -0400, Tom Lane wrote: Now that the index options infrastructure is in, I am having a couple of second thoughts about the specific behavior that's been implemented, particularly for btree fillfactor. 1. The btree build code (nbtsort.c) is dependent on the assumption that the fillfactor is at least 2/3rds. This is because we must put at least two keys in each page, and with maximally sized keys (1/3rd page) it might try to put only 0 or 1 tuple in a page if fillfactor is small. However, maximally sized keys are certainly a corner case, and in more usual situations a smaller fillfactor could be useful. I'm thinking we could change the nbtsort.c code to work like stop filling page when fillfactor is exceeded AND there are at least two entries already. Then any old fillfactor would work. I like the idea. Do you think there should be a way of packing certain indexes tighter, once they are known to be mostly read only? For example, an option on REINDEX? This would free PostgreSQL to use a smaller fillfactor while still allowing people to optimize those of their tables that would benefit from a higher fillfactor once they become mostly static? 3. What should the minimum fillfactor be? The patch as submitted set the minimum to 50% for all relation types. I'm inclined to think we should allow much lower fillfactors, maybe down to 10%. A really low fillfactor could be a good idea in a heavily updated table --- at least, I don't think we have any evidence to prove that it's not sane to want a fillfactor below 50%. If there was a way of packing relations tighter, allowing much lower fillfactors should be fine. Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] A couple thoughts about btree fillfactor
Ühel kenal päeval, E, 2006-07-10 kell 12:36, kirjutas Tom Lane: 3. What should the minimum fillfactor be? The patch as submitted set the minimum to 50% for all relation types. I'm inclined to think we should allow much lower fillfactors, maybe down to 10%. A really low fillfactor could be a good idea in a heavily updated table --- at least, I don't think we have any evidence to prove that it's not sane to want a fillfactor below 50%. Sure 50% is way too big as an lower limit. We may even want to have pages that have only 1 tuple in heavy update cases. So perhaps we should set the minimum to 1% or even 0.1% and apply similar logic you suggested for btree pages above, that is stop adding new ones when the threasold is reached. Comments? -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] A couple thoughts about btree fillfactor
On Mon, Jul 10, 2006 at 03:17:01PM -0400, Tom Lane wrote: [EMAIL PROTECTED] writes: ... Do you think there should be a way of packing certain indexes tighter, once they are known to be mostly read only? For example, an option on REINDEX? This would free PostgreSQL to use a smaller fillfactor while still allowing people to optimize those of their tables that would benefit from a higher fillfactor once they become mostly static? Isn't it sufficient to change the fillfactor and REINDEX? I've never tried that - if it works sure... :-) Thanks, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __ . . _ ._ . . .__. . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/|_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] A couple thoughts about btree fillfactor
[EMAIL PROTECTED] writes: ... Do you think there should be a way of packing certain indexes tighter, once they are known to be mostly read only? For example, an option on REINDEX? This would free PostgreSQL to use a smaller fillfactor while still allowing people to optimize those of their tables that would benefit from a higher fillfactor once they become mostly static? Isn't it sufficient to change the fillfactor and REINDEX? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
