Re: Size comparison between a Composite type and an

I made some tests with three different types:

numeric, text and a specialized type written in c.

The tests were made with 20 digit codes.

The specialized type was a struct defined as:

typdef struct mycode {
char c1;
char c2;
int32 c3;
int32 c4;
} mycode

The sizeof(mycode) returns 12 bytes so i assume this as storage size of
mycode type.
The mycode type was created with the following sql command:
CREATE TYPE mycode (
internallength = 12,
input = mycode_in,
output = mycode_out,
alignment = int
);

The text data type has a storage size of 20 bytes + 4 bytes overhead =
24 bytes.

The numeric data type has a storage size of 10 bytes + 8 bytes overhead
= 18 bytes.

I made three tables of one column using the three different data types
and checked the size in bytes of the three tables.

The results were not as expected.

I was expecting these theoretical results:
mycode: 1.000.000 of records => 12.000.000 bytes
numeric: 1.000.000 of records => 18.000.000 bytes
text: 1.000.000 of records => 24.000.000 bytes

That is the final size of the table with the text data type to be the
double of mycode type.

The real results were:
mycode: 1.000.000 of records => 65.159.168 bytes
numeric: 1.000.000 of records => 74.895.702 bytes
text: 1.000.000 of records => 77.340.672 bytes

The "text" table is only 16% larger than mycode one (I was expecting 100%!).

Any idea?

Thank you,
Denis

Douglas McNaught wrote:
> Denis Gasparin <denis(at)edistar(dot)com> writes:
>
>
>> If the composite data type has 4 bytes overhead, I save 4 bytes for
>> each number... that is important because I must store many many
>> numbers.
>>
>
> Yes, if size is a big issue you might be better off with a specialized
> type.
>
> -Doug
>
>