Postgres Pro
Downloads
Home   >   mailing lists

Thread: a bit strange btree index tuples

a bit strange btree index tuples

From
Tomas Vondra
Date:
Hi,

when working on the integrity checking tool, I've noticed there are a
bit trange tuples in the btree indexes. E.g. if I do this:

create table test_table (id int);
insert into test_table select i from generate_series(1,1000) s(i);
create index test_index on test_table(id);

then pageinspect returns this:

testdb=# select bt_page_items('test_index', 3);         bt_page_items
----------------------------------(1,"(1,1)",8,f,f,"")(2,"(2,1)",12,f,f,"ca 01 00 00")(3,"(4,1)",12,f,f,"93 03 00 00")
(3 rows)

I don't understand the first row and I've been unable to find out if
it's something special for the btree indexes or what.

According to ItemId in the PageHeader the tuple has these features
lp_flags=1 (LP_NORMAL)lp_off=8168lp_len=8 (i.e. exactly sizeof(IndexTuple)

and according to the IndexTuple, t_info=8 (so the length is 8 and it
does not have any NULL or varwidth attributes).

Yes, the lengths in page header and tuple match (8 in both cases) but
where are the attributes?

I've noticed there is yet another index tuple for ctid=(1,1), right on
the first page of the index

select bt_page_items('test_index', 1);...(255,"(0,254)",12,f,f,"fe 00 00 00")(256,"(0,255)",12,f,f,"ff 00 00
00")(257,"(1,1)",12,f,f,"0001 00 00")(258,"(1,2)",12,f,f,"01 01 00 00")...
 

but I still wonder what is the index tuple for.

regards
Tomas

Re: a bit strange btree index tuples

From
Tom Lane
Date:
Tomas Vondra <tv@fuzzy.cz> writes:
> testdb=# select bt_page_items('test_index', 3);
>           bt_page_items
> ----------------------------------
>  (1,"(1,1)",8,f,f,"")
>  (2,"(2,1)",12,f,f,"ca 01 00 00")
>  (3,"(4,1)",12,f,f,"93 03 00 00")
> (3 rows)

> I don't understand the first row and I've been unable to find out if
> it's something special for the btree indexes or what.

You should read src/backend/access/nbtree/README, which would explain to
you why it is that leftmost tuples on interior btree pages don't contain
key values.
        regards, tom lane

Re: a bit strange btree index tuples

From
Tomas Vondra
Date:
Dne 1.5.2011 05:30, Tom Lane napsal(a):
> Tomas Vondra <tv@fuzzy.cz> writes:
>> testdb=# select bt_page_items('test_index', 3);
>>           bt_page_items
>> ----------------------------------
>>  (1,"(1,1)",8,f,f,"")
>>  (2,"(2,1)",12,f,f,"ca 01 00 00")
>>  (3,"(4,1)",12,f,f,"93 03 00 00")
>> (3 rows)
> 
>> I don't understand the first row and I've been unable to find out if
>> it's something special for the btree indexes or what.
> 
> You should read src/backend/access/nbtree/README, which would explain to
> you why it is that leftmost tuples on interior btree pages don't contain
> key values.

Thanks, I somehow missed that doc yesterday (ok, it was 3 AM so it's
understandable). I guess the last paragraph in "Notes About Data
Representation" explains this behaviour.

Tomas