public inbox for [email protected]
help / color / mirror / Atom feedPageinspect bt_metap help
5+ messages / 3 participants
[nested] [flat]
* Pageinspect bt_metap help
@ 2017-09-17 21:52 Neto pr <[email protected]>
2017-09-17 21:59 ` Re: Pageinspect bt_metap help Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Neto pr @ 2017-09-17 21:52 UTC (permalink / raw)
To: pgsql-performance
Hello All
I am using Postgresql extension pageinspect.
Could someone tell me the meaning of these columns: magic, version, root,
level, fastroot, fastlevel of the bt_metap function.
This information is not presents in the documentation.
The height of the b-tree (position of node farthest from root to leaf), is
the column Level?
See below a return query that I ran on an index called
idx_l_shipmodelineitem000
------------------------------------------------------------------
postgres # SELECT * FROM bt_metap ('idx_l_shipmodelineitem000');
postgres # magic | version | root | level | fastroot | fastlevel
postgres # 340322 | 2 | 41827 | 3 | 41827 | 3
Best regards
Neto
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Pageinspect bt_metap help
2017-09-17 21:52 Pageinspect bt_metap help Neto pr <[email protected]>
@ 2017-09-17 21:59 ` Peter Geoghegan <[email protected]>
2017-09-18 14:31 ` Re: Pageinspect bt_metap help Neto pr <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Peter Geoghegan @ 2017-09-17 21:59 UTC (permalink / raw)
To: Neto pr <[email protected]>; +Cc: pgsql-performance
On Sun, Sep 17, 2017 at 2:52 PM, Neto pr <[email protected]> wrote:
> I am using Postgresql extension pageinspect.
>
> Could someone tell me the meaning of these columns: magic, version, root,
> level, fastroot, fastlevel of the bt_metap function.
>
> This information is not presents in the documentation.
A magic number distinguishes the meta-page as a B-Tree meta-page. A
version number is used for each major incompatible revision of the
B-Tree code (these are very infrequent).
The fast root can differ from the true root following a deletion
pattern that leaves a "skinny index". The implementation can never
remove a level, essentially because it's optimized for concurrency,
though it can have a fast root, to just skip levels. This happens to
levels that no longer contain any distinguishing information in their
single internal page.
I imagine that in practice the large majority of B-Trees never have a
true root that differs from its fast root - you see this with repeated
large range deletions. Probably nothing to worry about.
> The height of the b-tree (position of node farthest from root to leaf), is
> the column Level?
Yes.
If you want to learn more about the B-Tree code, I suggest that you
start by looking at the code for contrib/amcheck.
--
Peter Geoghegan
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Pageinspect bt_metap help
2017-09-17 21:52 Pageinspect bt_metap help Neto pr <[email protected]>
2017-09-17 21:59 ` Re: Pageinspect bt_metap help Peter Geoghegan <[email protected]>
@ 2017-09-18 14:31 ` Neto pr <[email protected]>
2017-09-19 01:07 ` Re: Pageinspect bt_metap help Peter Geoghegan <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Neto pr @ 2017-09-18 14:31 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: pgsql-performance
Very interesting information.
See if I'm right, so for performance purposes, would it be better to
consider the columns: fast_root and fast_level instead of the root and
level columns?
I have read that even deleting records the B-tree tree is not rebuilt, so
it does not cause overhead in dbms, and can have null pointers.
In my example, the values of fast_root, fast_root are equal to root,
level, I believe that due to the newly created index and no delete
operations occurred in the table.
Best Regards
Neto
2017-09-17 18:59 GMT-03:00 Peter Geoghegan <[email protected]>:
> On Sun, Sep 17, 2017 at 2:52 PM, Neto pr <[email protected]> wrote:
> > I am using Postgresql extension pageinspect.
> >
> > Could someone tell me the meaning of these columns: magic, version, root,
> > level, fastroot, fastlevel of the bt_metap function.
> >
> > This information is not presents in the documentation.
>
> A magic number distinguishes the meta-page as a B-Tree meta-page. A
> version number is used for each major incompatible revision of the
> B-Tree code (these are very infrequent).
>
> The fast root can differ from the true root following a deletion
> pattern that leaves a "skinny index". The implementation can never
> remove a level, essentially because it's optimized for concurrency,
> though it can have a fast root, to just skip levels. This happens to
> levels that no longer contain any distinguishing information in their
> single internal page.
>
> I imagine that in practice the large majority of B-Trees never have a
> true root that differs from its fast root - you see this with repeated
> large range deletions. Probably nothing to worry about.
>
> > The height of the b-tree (position of node farthest from root to leaf),
> is
> > the column Level?
>
> Yes.
>
> If you want to learn more about the B-Tree code, I suggest that you
> start by looking at the code for contrib/amcheck.
>
> --
> Peter Geoghegan
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Pageinspect bt_metap help
2017-09-17 21:52 Pageinspect bt_metap help Neto pr <[email protected]>
2017-09-17 21:59 ` Re: Pageinspect bt_metap help Peter Geoghegan <[email protected]>
2017-09-18 14:31 ` Re: Pageinspect bt_metap help Neto pr <[email protected]>
@ 2017-09-19 01:07 ` Peter Geoghegan <[email protected]>
2017-09-19 03:28 ` Re: Pageinspect bt_metap help Tom Lane <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Peter Geoghegan @ 2017-09-19 01:07 UTC (permalink / raw)
To: Neto pr <[email protected]>; +Cc: pgsql-performance
On Mon, Sep 18, 2017 at 7:31 AM, Neto pr <[email protected]> wrote:
> In my example, the values of fast_root, fast_root are equal to root, level,
> I believe that due to the newly created index and no delete operations
> occurred in the table.
Fast root and true root will probably never be different, even when
there are many deletions, including page deletions by VACUUM. As I
understand it, the fast root thing is for a fairly rare, though still
important edge case. It's a way of working around the fact that a
B-Tree can never become shorter due to the locking protocols not
allowing it. We can instead just pretend that it's shorter, knowing
that upper levels don't contain useful information.
--
Peter Geoghegan
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Pageinspect bt_metap help
2017-09-17 21:52 Pageinspect bt_metap help Neto pr <[email protected]>
2017-09-17 21:59 ` Re: Pageinspect bt_metap help Peter Geoghegan <[email protected]>
2017-09-18 14:31 ` Re: Pageinspect bt_metap help Neto pr <[email protected]>
2017-09-19 01:07 ` Re: Pageinspect bt_metap help Peter Geoghegan <[email protected]>
@ 2017-09-19 03:28 ` Tom Lane <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2017-09-19 03:28 UTC (permalink / raw)
To: Peter Geoghegan <[email protected]>; +Cc: Neto pr <[email protected]>; pgsql-performance
Peter Geoghegan <[email protected]> writes:
> On Mon, Sep 18, 2017 at 7:31 AM, Neto pr <[email protected]> wrote:
>> In my example, the values of fast_root, fast_root are equal to root, level,
>> I believe that due to the newly created index and no delete operations
>> occurred in the table.
> Fast root and true root will probably never be different, even when
> there are many deletions, including page deletions by VACUUM. As I
> understand it, the fast root thing is for a fairly rare, though still
> important edge case. It's a way of working around the fact that a
> B-Tree can never become shorter due to the locking protocols not
> allowing it. We can instead just pretend that it's shorter, knowing
> that upper levels don't contain useful information.
My (vague) recollection is that it's actually useful in cases where the
live key-space constantly migrates to the right, so that the original
upper-level key splits would become impossibly unbalanced. This isn't
all that unusual a situation; consider timestamp keys for instance,
in a table where old data gets flushed regularly.
regards, tom lane
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-09-19 03:28 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-09-17 21:52 Pageinspect bt_metap help Neto pr <[email protected]>
2017-09-17 21:59 ` Peter Geoghegan <[email protected]>
2017-09-18 14:31 ` Neto pr <[email protected]>
2017-09-19 01:07 ` Peter Geoghegan <[email protected]>
2017-09-19 03:28 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox