MIME-Version: 1.0
In-Reply-To: 
 <CAH2-Wzka8Xkb79T5TbTeHPch2dTpWY+HsViqNLtKSKXY9bzOFA@mail.gmail.com>
References: 
 <CA+wPC0MRuc0qn6v57VNtBE0Dh-r1Bcugi4FHHqC_kY-1X2tzGQ@mail.gmail.com>
 <CAH2-Wzka8Xkb79T5TbTeHPch2dTpWY+HsViqNLtKSKXY9bzOFA@mail.gmail.com>
From: Neto pr <netopr9@gmail.com>
Date: Mon, 18 Sep 2017 11:31:47 -0300
Message-ID: 
 <CA+wPC0NbHJ=awqjkq-GzosQ1r=nGN=2=zk6x=7btVCk7=4SABg@mail.gmail.com>
Subject: Re: Pageinspect bt_metap help
To: Peter Geoghegan <pg@bowt.ie>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Content-Type: multipart/alternative; boundary="f4030435b2304908590559779aa7"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--f4030435b2304908590559779aa7
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 =E2=80=8B=E2=80=8Bof fast_root, fast_root are equ=
al 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 <pg@bowt.ie>:

> On Sun, Sep 17, 2017 at 2:52 PM, Neto pr <netopr9@gmail.com> wrote:
> > I am using Postgresql extension pageinspect.
> >
> > Could someone tell me the meaning of these columns: magic, version, roo=
t,
> > 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
>

--f4030435b2304908590559779aa7
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Very interesting information.</div><div>See if I&#39;=
m right, so for performance purposes, would it be better to consider the co=
lumns: fast_root and fast_level instead of the root and level columns?</div=
><div><br></div><div>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 p=
ointers.</div><div><br></div><div>In my example, the values =E2=80=8B=E2=80=
=8Bof 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.</di=
v><div><br></div><div>Best Regards=C2=A0</div><div>Neto</div></div><div cla=
ss=3D"gmail_extra"><br><div class=3D"gmail_quote">2017-09-17 18:59 GMT-03:0=
0 Peter Geoghegan <span dir=3D"ltr">&lt;<a href=3D"mailto:pg@bowt.ie" targe=
t=3D"_blank">pg@bowt.ie</a>&gt;</span>:<br><blockquote class=3D"gmail_quote=
" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><=
span class=3D"">On Sun, Sep 17, 2017 at 2:52 PM, Neto pr &lt;<a href=3D"mai=
lto:netopr9@gmail.com">netopr9@gmail.com</a>&gt; wrote:<br>
&gt; I am using Postgresql extension pageinspect.<br>
&gt;<br>
&gt; Could someone tell me the meaning of these columns: magic, version, ro=
ot,<br>
&gt; level, fastroot, fastlevel of the bt_metap function.<br>
&gt;<br>
&gt; This information is not presents in the documentation.<br>
<br>
</span>A magic number distinguishes the meta-page as a B-Tree meta-page. A<=
br>
version number is used for each major incompatible revision of the<br>
B-Tree code (these are very infrequent).<br>
<br>
The fast root can differ from the true root following a deletion<br>
pattern that leaves a &quot;skinny index&quot;. The implementation can neve=
r<br>
remove a level, essentially because it&#39;s optimized for concurrency,<br>
though it can have a fast root, to just skip levels. This happens to<br>
levels that no longer contain any distinguishing information in their<br>
single internal page.<br>
<br>
I imagine that in practice the large majority of B-Trees never have a<br>
true root that differs from its fast root - you see this with repeated<br>
large range deletions. Probably nothing to worry about.<br>
<span class=3D""><br>
&gt; The height of the b-tree (position of node farthest from root to leaf)=
, is<br>
&gt; the column Level?<br>
<br>
</span>Yes.<br>
<br>
If you want to learn more about the B-Tree code, I suggest that you<br>
start by looking at the code for contrib/amcheck.<br>
<span class=3D"HOEnZb"><font color=3D"#888888"><br>
--<br>
Peter Geoghegan<br>
</font></span></blockquote></div><br></div>

--f4030435b2304908590559779aa7--