pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
B-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