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 ----- Original Message ---- From: Erik Jones <[EMAIL PROTECTED]> To: SHARMILA JOTHIRAJAH <[EMAIL PROTECTED]> Cc: pgsql-general@postgresql.org Sent: Wednesday, November 21, 2007 11:38:44 AM Subject: Re: [GENERAL] 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® [EMAIL PROTECTED] 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 ____________________________________________________________________________________ Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
