pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedB-tree index balance?
3+ messages / 2 participants
[nested] [flat]
* B-tree index balance?
@ 2024-01-19 16:24 Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Ron Johnson @ 2024-01-19 16:24 UTC (permalink / raw)
To: pgsql-general
On an RDMS which I used in the 1990s and 2000s, b-tree indices of sequences
would get unbalanced, since every new leaf was added to the far right
corner of the tree.
Sure, they would auto-balance *to a degree* during node splits, but all
those "far-right corner" inserts still left them pretty lopsided.
Thus, they provided a utility which we could use to determine the
lopsidedness, and thus decide when to rebuild an index.
Does Postgresql keep b-tree indexes on sequences fully balanced? If not,
how do I see how unbalanced they are? (Assume PG12+.)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: B-tree index balance?
@ 2024-01-19 16:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2024-01-19 16:37 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: pgsql-general
Ron Johnson <ronljohnsonjr@gmail.com> writes:
> On an RDMS which I used in the 1990s and 2000s, b-tree indices of sequences
> would get unbalanced, since every new leaf was added to the far right
> corner of the tree.
> Sure, they would auto-balance *to a degree* during node splits, but all
> those "far-right corner" inserts still left them pretty lopsided.
> Thus, they provided a utility which we could use to determine the
> lopsidedness, and thus decide when to rebuild an index.
> Does Postgresql keep b-tree indexes on sequences fully balanced? If not,
> how do I see how unbalanced they are? (Assume PG12+.)
As far as I know, we don't have a problem of that sort. Continued
insertions will eventually force a split of the root node, which will
rebalance the tree.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: B-tree index balance?
@ 2024-01-19 21:36 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 3+ messages in thread
From: Ron Johnson @ 2024-01-19 21:36 UTC (permalink / raw)
To: pgsql-general
On Fri, Jan 19, 2024 at 11:37 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Ron Johnson <ronljohnsonjr@gmail.com> writes:
> > On an RDMS which I used in the 1990s and 2000s, b-tree indices of
> sequences
> > would get unbalanced, since every new leaf was added to the far right
> > corner of the tree.
> > Sure, they would auto-balance *to a degree* during node splits, but all
> > those "far-right corner" inserts still left them pretty lopsided.
> > Thus, they provided a utility which we could use to determine the
> > lopsidedness, and thus decide when to rebuild an index.
>
> > Does Postgresql keep b-tree indexes on sequences fully balanced? If not,
> > how do I see how unbalanced they are? (Assume PG12+.)
>
> As far as I know, we don't have a problem of that sort. Continued
> insertions will eventually force a split of the root node, which will
> rebalance the tree.
>
Thanks for the explanation.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-01-19 21:36 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-19 16:24 B-tree index balance? Ron Johnson <ronljohnsonjr@gmail.com>
2024-01-19 16:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-19 21:36 ` Ron Johnson <ronljohnsonjr@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox