Thread: storage size of "bit" data type..
Hi, i'm trying to find out the storage size for bit(n) data. My initial assumption would be that for any 8 bits, one byte of storage is required. Is this assumption correct? I didn't find that information in the online docs. thanks, Alex
On Dec 5, 2007, at 14:19 , Alex Mayrhofer wrote:
> Hi,
>
> i'm trying to find out the storage size for bit(n) data. My initial
> assumption would be that for any 8 bits, one byte of storage is
> required.
select pg_column_size(B'1') as "1bit",
pg_column_size(B'1111') as "4bits",
pg_column_size(B'11111111') as "1byte",
pg_column_size(B'111111111111') as "12bits",
pg_column_size(B'1111111111111111') as "2bytes",
pg_column_size(B'11111111111111111') as "17bits",
pg_column_size(B'111111111111111111111111') as "3bytes";
1bit | 4bits | 1byte | 12bits | 2bytes | 17bits | 3bytes
------+-------+-------+--------+--------+--------+--------
9 | 9 | 9 | 10 | 10 | 11 | 11
(1 row)
Looks like there's 8 bytes of overhead as well, probably because a
bit string is a varlena type.
Michael Glaesemann
grzm seespotcode net
On Dec 5, 2007, at 7:23 PM, Michael Glaesemann wrote:
> On Dec 5, 2007, at 14:19 , Alex Mayrhofer wrote:
>> i'm trying to find out the storage size for bit(n) data. My
>> initial assumption would be that for any 8 bits, one byte of
>> storage is required.
>
> select pg_column_size(B'1') as "1bit",
> pg_column_size(B'1111') as "4bits",
> pg_column_size(B'11111111') as "1byte",
> pg_column_size(B'111111111111') as "12bits",
> pg_column_size(B'1111111111111111') as "2bytes",
> pg_column_size(B'11111111111111111') as "17bits",
> pg_column_size(B'111111111111111111111111') as "3bytes";
> 1bit | 4bits | 1byte | 12bits | 2bytes | 17bits | 3bytes
> ------+-------+-------+--------+--------+--------+--------
> 9 | 9 | 9 | 10 | 10 | 11 | 11
> (1 row)
>
> Looks like there's 8 bytes of overhead as well, probably because a
> bit string is a varlena type.
Wow, that's screwed up... that's a lot more than varlena overhead:
select pg_column_size('a'::text), pg_column_size(1::numeric),
pg_column_size(3111234::numeric);
pg_column_size | pg_column_size | pg_column_size
----------------+----------------+----------------
5 | 10 | 12
Apparently it's something related to numeric.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Attachment
"Decibel!" <decibel@decibel.org> writes:
> On Dec 5, 2007, at 7:23 PM, Michael Glaesemann wrote:
>> On Dec 5, 2007, at 14:19 , Alex Mayrhofer wrote:
>>> i'm trying to find out the storage size for bit(n) data. My initial
>>> assumption would be that for any 8 bits, one byte of storage is required.
>>
>> select pg_column_size(B'1') as "1bit",
>> pg_column_size(B'1111') as "4bits",
>> pg_column_size(B'11111111') as "1byte",
>> pg_column_size(B'111111111111') as "12bits",
>> pg_column_size(B'1111111111111111') as "2bytes",
>> pg_column_size(B'11111111111111111') as "17bits",
>> pg_column_size(B'111111111111111111111111') as "3bytes";
>> 1bit | 4bits | 1byte | 12bits | 2bytes | 17bits | 3bytes
>> ------+-------+-------+--------+--------+--------+--------
>> 9 | 9 | 9 | 10 | 10 | 11 | 11
>> (1 row)
>>
>> Looks like there's 8 bytes of overhead as well, probably because a bit
>> string is a varlena type.
>
> Wow, that's screwed up... that's a lot more than varlena overhead:
It needs to store the number of bits present as well. Otherwise it wouldn't be
able to tell apart B'1' and B'01' ... B'00000001'
> select pg_column_size('a'::text), pg_column_size(1::numeric),
> pg_column_size(3111234::numeric);
> pg_column_size | pg_column_size | pg_column_size
> ----------------+----------------+----------------
> 5 | 10 | 12
>
> Apparently it's something related to numeric.
Only in the sense that numeric also has to store some meta data as well like
the weight and display precision.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning
On Dec 6, 2007, at 5:19 PM, Vyacheslav Kalinin wrote: > > It needs to store the number of bits present as well > > Couldn't that be reduced to 1 byte that'd say how many bits count > in the last byte? > > > Only in the sense that numeric also has to store some meta data > as well like > the weight and display precision. > > Is it really necessary to store display precision when it can be > taken from the table column definition? Two problems... 1) CREATE TABLE n(n numeric); 2) The knowledge of extra type information (ie: the numbers in char() or numeric()) don't extend deeply enough into the code. This is part of why char() uses the exact same storage mechanism as varchar(). -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Attachment
On Dec 6, 2007, at 5:10 PM, Gregory Stark wrote: > It needs to store the number of bits present as well. Otherwise it > wouldn't be > able to tell apart B'1' and B'01' ... B'00000001' ... > Only in the sense that numeric also has to store some meta data as > well like > the weight and display precision. Hrm... perhaps that's another worthwhile target for the varvarlena technique... -- Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828
Attachment
> It needs to store the number of bits present as well
Couldn't that be reduced to 1 byte that'd say how many bits count in the last byte?
> Only in the sense that numeric also has to store some meta data as well like
the weight and display precision.
Is it really necessary to store display precision when it can be taken from the table column definition?
Couldn't that be reduced to 1 byte that'd say how many bits count in the last byte?
> Only in the sense that numeric also has to store some meta data as well like
the weight and display precision.
Is it really necessary to store display precision when it can be taken from the table column definition?
Added to TODO: * Reduce BIT data type overhead using short varlena headers http://archives.postgresql.org/pgsql-general/2007-12/msg00273.php --------------------------------------------------------------------------- Decibel! wrote: > On Dec 5, 2007, at 7:23 PM, Michael Glaesemann wrote: > > On Dec 5, 2007, at 14:19 , Alex Mayrhofer wrote: > >> i'm trying to find out the storage size for bit(n) data. My > >> initial assumption would be that for any 8 bits, one byte of > >> storage is required. > > > > select pg_column_size(B'1') as "1bit", > > pg_column_size(B'1111') as "4bits", > > pg_column_size(B'11111111') as "1byte", > > pg_column_size(B'111111111111') as "12bits", > > pg_column_size(B'1111111111111111') as "2bytes", > > pg_column_size(B'11111111111111111') as "17bits", > > pg_column_size(B'111111111111111111111111') as "3bytes"; > > 1bit | 4bits | 1byte | 12bits | 2bytes | 17bits | 3bytes > > ------+-------+-------+--------+--------+--------+-------- > > 9 | 9 | 9 | 10 | 10 | 11 | 11 > > (1 row) > > > > Looks like there's 8 bytes of overhead as well, probably because a > > bit string is a varlena type. > > Wow, that's screwed up... that's a lot more than varlena overhead: > > select pg_column_size('a'::text), pg_column_size(1::numeric), > pg_column_size(3111234::numeric); > pg_column_size | pg_column_size | pg_column_size > ----------------+----------------+---------------- > 5 | 10 | 12 > > Apparently it's something related to numeric. > -- > Decibel!, aka Jim C. Nasby, Database Architect decibel@decibel.org > Give your computer some brain candy! www.distributed.net Team #1828 > > -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +