I vacuumed the table before I got this relpages count. When i make
the
change tat you mentioned the total table size as per my calculation is
1373780 and the result thro pg_relation_size is 1548288.
What other overheads are there for the table? Im not sure how to find the free space? But I didnot delete/insert/update any rows after the first insertion.
Thanks
sharmila
What other overheads are there for the table? Im not sure how to find the free space? But I didnot delete/insert/update any rows after the first insertion.
Thanks
sharmila
----- Original Message ----
From: Erik Jones <erik@xxxxxxxxxx>
To: SHARMILA JOTHIRAJAH <sharmi_jo@xxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Sent: Wednesday, November 21, 2007 11:38:44 AM
Subject: Re: Postgres table size
On Nov 21, 2007, at 9:17 AM, SHARMILA JOTHIRAJAH wrote:
> Hi
> Thanks. Ill post it on the mailing list when I get the results. Im
> trying to calculate and see how the tablesize works for a simple
> table.
>
> I have a table with 10 cols
> 5 varchars _ it is declared as varchar(40) but contains data of
> length 3
> 5 numeric - declared as numeric(22,0) but contains data of
> precision 10
> There are 10000 rows
>
> select * from pg_relation_size gives 1548288 bytes as the table's size
> select relpages from pg_class for that table gives 189 pages
>
> Calculation
> varchar = (overhead) 4 + (actual length of string) 3 = 7 bytes
> *for 5 varchar cols =
> 5*7 = 35 bytes
>
> numeric (according to manual--- The actual storage requirement is
> two bytes for each group of four decimal digits, plus eight bytes
> overhead )
> numeric = ( 10/4)*2 +8 = 13 bytes
> *for 5 numeric cols = 13 *5 = 65
Not that it will make much difference, but you need to round up in
the 10/4 part so you get 14 bytes, not 13.
>
> *row overhead = 32
>
> So
> Bytes per row = 35 + 65 +32 = 132 bytes
> for 10000 rows = 1320000
>
> The manual says ---The first 20 bytes of each page consists of a
> page header (PageHeaderData).
> There are 189 pages
> *page header cost = 20*189 = 3780 bytes
>
> Therefore Total = 1320000 (row cost) + 3780 (page header cost)
> =1323780 bytes for this table
>
> Is this calculation right? But the size of the table according to
> pg_relation_size is 1548288 bytes
>
> What am I missing in my calculation?
One thing to note is that the relpages value is only exact from the
time of a vacuum until the next dml statement on the table, i.e.
relpages is not updated after inserts, update, and deletes, just
after vacuums.
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
From: Erik Jones <erik@xxxxxxxxxx>
To: SHARMILA JOTHIRAJAH <sharmi_jo@xxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxx
Sent: Wednesday, November 21, 2007 11:38:44 AM
Subject: Re: Postgres table size
On Nov 21, 2007, at 9:17 AM, SHARMILA JOTHIRAJAH wrote:
> Hi
> Thanks. Ill post it on the mailing list when I get the results. Im
> trying to calculate and see how the tablesize works for a simple
> table.
>
> I have a table with 10 cols
> 5 varchars _ it is declared as varchar(40) but contains data of
> length 3
> 5 numeric - declared as numeric(22,0) but contains data of
> precision 10
> There are 10000 rows
>
> select * from pg_relation_size gives 1548288 bytes as the table's size
> select relpages from pg_class for that table gives 189 pages
>
> Calculation
> varchar = (overhead) 4 + (actual length of string) 3 = 7 bytes
> *for 5 varchar cols =
> 5*7 = 35 bytes
>
> numeric (according to manual--- The actual storage requirement is
> two bytes for each group of four decimal digits, plus eight bytes
> overhead )
> numeric = ( 10/4)*2 +8 = 13 bytes
> *for 5 numeric cols = 13 *5 = 65
Not that it will make much difference, but you need to round up in
the 10/4 part so you get 14 bytes, not 13.
>
> *row overhead = 32
>
> So
> Bytes per row = 35 + 65 +32 = 132 bytes
> for 10000 rows = 1320000
>
> The manual says ---The first 20 bytes of each page consists of a
> page header (PageHeaderData).
> There are 189 pages
> *page header cost = 20*189 = 3780 bytes
>
> Therefore Total = 1320000 (row cost) + 3780 (page header cost)
> =1323780 bytes for this table
>
> Is this calculation right? But the size of the table according to
> pg_relation_size is 1548288 bytes
>
> What am I missing in my calculation?
One thing to note is that the relpages value is only exact from the
time of a vacuum until the next dml statement on the table, i.e.
relpages is not updated after inserts, update, and deletes, just
after vacuums.
Erik Jones
Software Developer | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Get easy, one-click access to your favorites. Make Yahoo! your homepage.
