Hi,
I have a table
CREATE TABLE "MsgCommon"
(
"UTC" timestamp without time zone NOT NULL,
"UID" bigint NOT NULL,
"DataSourceID" integer NOT NULL,
"DataSourceType" integer NOT NULL,
"BSCArchive" boolean NOT NULL,
"Oddities" integer NOT NULL,
"Encapsulated" boolean NOT NULL,
"Formatter" character(3),
"MMSI" integer
);
with 3358604 rows.
I’m confused with the table size which is 245 MB.
Simple calculations show that each row occupies 76 bytes approximately.
But anticipated row size would be 41 or near.
select
pg_column_size( '2001-01-01'::timestamp without time zone ) +
pg_column_size( 0::bigint ) +
pg_column_size( 0::integer ) +
pg_column_size( 0::integer ) +
pg_column_size( true ) +
pg_column_size( 0::integer ) +
pg_column_size( true ) +
pg_column_size( '0'::character(3) ) +
pg_column_size( 0::integer );
Does the presence of HeapTupleHeaderData, ‘null bitmap’, aligning to MAXALIGN distance and other additional per row/page fields explain this difference (as described in “53.3. Database Page Layout”)?
Is there a way to reduce these overheads?
It may seem strange to you but in MSSQLServer2005 the same table occupies 150 MB only (47 bytes per row).
Thanks in advance,
Zubkovsky Sergey