Thread: 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
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
--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | 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
bindthem...
http://mark.mielke.cc/
Ü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
On Mon, Jul 10, 2006 at 03:17:01PM -0400, Tom Lane wrote:
> mark@mark.mielke.cc 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
--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | 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
bindthem...
http://mark.mielke.cc/
mark@mark.mielke.cc 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
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