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@xxxxxxxxxxx> 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
