Re: FILLFACTOR and increasing index

Dne 9.5.2011 17:25, Leonardo Francalanci napsal(a):
>> It will be really useful to see some test results where you alter the
>> fillfactor and report various measurables.
>
>
> It's not that easy... stressing "only" the index insertion
> speed won't be simple. I would have liked some "theory"...
> The docs seem to imply there are some guidelines, it's
> just that it's too cryptic:
>
> "for heavily updated tables a smaller fillfactor is better
> to minimize the need for page splits"
>
>
> "heavily updated" -> does it mean tables that are inserted/updated
> or only "updated"???

Well, an UPDATE is actually DELETE+INSERT (that's how PostgreSQL MVCC
works). It may be a bit more complicated with HOT, but that's not your
case, as you're only inserting data.

> "leaf pages are filled to this percentage [...] when extending the index
> at the right (adding new largest key values)."

Hmmm, not sure how exactly this works, but I guess that if you're only
inserting data then fillfactor=100 is the right thing. I believe it
kicks in only when you need to insert data into an 'old' leaf page. If
the page is full, then it needs to be split but if you reserve some free
space (using e.g. fillfactor=80) then the split is not needed.

> Does it mean that since I will (almost) always add new largest key
> values, I should have a big or small FILLFACTOR???

I'd go with the fillfactor=100.

> I know that theory is one thing and real testing another; but I can't
> test everything; if there are some (proved?) guidelines I'd like to
> use them (example: I'm not going to test that fillfactor in table creation
> in my case won't make any difference in performance; I trust the
> docs and the fact that "it makes sense").

Yes, I use the same approach, but I'm not aware of any such guideline
related to fillfactor with indexes. Anyway those guidelines need to be
written by someone, so you have a great opportunity ;-)

regards
Tomas