>>> 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.
>Also, this calculation is ignoring the fact that (pre-8.3) varlena >values have to be int-aligned, so there's wasted pad space too. >The varchar values really need 8 bytes each, and the numeric values >16, so the actual data payload in each row is 120 bytes. Then add >HeapTupleHeader (28 bytes), and then MAXALIGN the whole row size. >So the rowsize would be either 148 or 152 bytes depending on if you >were on a machine with 8-byte MAXALIGN. Then add the per-row item >pointer, giving total per-row space of 152 or 156 bytes. That >means you can fit either 53 or 52 rows per page, giving either 188 >or 192 pages as the minimum possible file size. Evidently, Sharmila >is using a MAXALIGN=4 machine and has a few dead rows in there.
Thanks for the detailed explanation Tom. You are rigt...The number of pages for this tables as per pg_class is 189.
I have a few questions 1. How do you find the MAXALIGN of the machine? And what is that used for? 2. How does null columns account for this space. For example, if I have the same table (10 cols --5 varchars and 5 numerics) in which 8 cols are null and 2 cols(1 varchar and 1 num) are not null, how is the storage space affected for this case?
