Row size overhead

From: "Zubkovsky, Sergey" <Sergey(dot)Zubkovsky(at)transas(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Row size overhead
Date: 2008-03-19 16:59:52
Message-ID: 528853D3C5ED2C4AA8990B504BA7FB850106DF1F@sol.transas.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Pavan Deolasee 2008-03-19 17:23:03 Re: Row size overhead
Previous Message Volkan YAZICI 2008-03-19 15:51:50 Re: array_cat without duplicity