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_deletionsIlya Kazakevich
JetBrains
http://www.jetbrains.com
The Drive to Develop
Thank you. So if that is the reason changing the fillfactor parameter should help?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
W dniu 2016-08-18 14:19:25 użytkownik Ilya Kazakevich <Ilya.Kazakevich@JetBrains.com> napisał:
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--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
From link you have pasted:
"Both insertions and deletions are fast as long as space is available on a block. If an insertion won't fit on the block, then some free space on some nearby block must be found and the auxiliary indices adjusted. The hope is that enough space is nearby such that a lot of blocks do not need to be reorganized."
and from postgres documentation:
fillfactor
The fillfactor for an index is a percentage that determines how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index build, and also when extending the index at the right (adding new largest key values)
So spliting happens when no room left on the page. But before that room can be used for further insertions...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Resolved by subject fallback