Thread: Re: Sequential vs. random values - number of pages in B-tree

W dniu 2016-08-18 14:00:31 użytkownik Ilya Kazakevich <Ilya.Kazakevich@JetBrains.com> napisał:
> Hi,
>
> >What's the reason that postgres needs more index pages to store random
> >data
> >than sequential ones?
>
> I assume that is because B-Tree is self-balanced tree, so it needs to be
> rebalanced after each insertion.
> Random insertions may go to the head of index where no space left leading to
> huge data moving.
> https://en.wikipedia.org/wiki/B-tree#Insertions_and_deletions
>
>
>
> Ilya Kazakevich
>
> JetBrains
> http://www.jetbrains.com
> The Drive to Develop
>
>

Thank you. So if that is the reason changing the fillfactor parameter should help?

>Thank you. So if that is the reason changing the fillfactor parameter should
>help?

Fillfactor is not about rebalancing, but about page split. If you have many insertions you may decrease fillfactor to
minimize page splits, but I am not sure it will help in your case.  But you should try) 
Better approach is to create index _after_ insertion, but it is not always possible.


Ilya Kazakevich

JetBrains
http://www.jetbrains.com
The Drive to Develop