Thread: How many levels a B-tree has?
Hi,[I'm not sure if this will be the best word to express the thing I mean, because I've been reading some papers, and mail archives that have been looked for by this word, and I haven't got any result, so I would do my best.]I need to know (if it possible) how many LEVELS a B-tree index has (to know how many accesses to the disk it would do in a query). I could know the index size, but no-idea to find out how many levels it has. I've read about pg_index, but there isn't any about.There is any way to do it?ThanksCris..
"Cris" <cris@dmcid.net> writes: > I need to know (if it possible) how many LEVELS a B-tree index > has (to know how many accesses to the disk it would do in a query). In CVS tip we keep track of that information in the index's metapage (page zero). But in so-far-released versions it's not explicitly tracked anywhere. You'd have to actually chase down the tree from the root to a leaf to count the levels. regards, tom lane
On Wed, May 14, 2003 at 12:42:17PM -0400, Tom Lane wrote: > "Cris" <cris@dmcid.net> writes: > > I need to know (if it possible) how many LEVELS a B-tree index > > has (to know how many accesses to the disk it would do in a query). > > In CVS tip we keep track of that information in the index's metapage > (page zero). But in so-far-released versions it's not explicitly > tracked anywhere. You'd have to actually chase down the tree from the > root to a leaf to count the levels. Does this level count takes into consideration the fast root of the tree? I think it doesn't. If this is so, the number of disk accesses will be overestimated by reading only the level count. One should traverse levels down from the true root to the fast root and substract that from the level count. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "El dia que dejes de cambiar dejaras de vivir"
Alvaro Herrera <alvherre@dcc.uchile.cl> writes: > On Wed, May 14, 2003 at 12:42:17PM -0400, Tom Lane wrote: >> In CVS tip we keep track of that information in the index's metapage >> (page zero). But in so-far-released versions it's not explicitly >> tracked anywhere. You'd have to actually chase down the tree from the >> root to a leaf to count the levels. > Does this level count takes into consideration the fast root of the > tree? I think it doesn't. IIRC we store the levels of both the true root and the fast root in the metapage. > If this is so, the number of disk accesses will be overestimated by > reading only the level count. One should traverse levels down from the > true root to the fast root and substract that from the level count. You're correct, the fast-root level is the interesting one for performance estimates. regards, tom lane