Erik Jones <erik@myemma.com> writes: > On Nov 21, 2007, at 9:17 AM, SHARMILA JOTHIRAJAH wrote: >> 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. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings

SHARMILA JOTHIRAJAH <sharmi_jo@yahoo.com> writes: > 1. How do you find the MAXALIGN of the machine? And what is that used for? pg_controldata will show "maximum data alignment". A rule of thumb is that it's 4 on 32-bit machines and 8 on 64-bit machines, but there are exceptions. > 2. How does null columns account for this space. If there are any nulls in a row then you pay for a null bitmap with 1 bit/column, but the null columns themselves aren't stored and hence take zero space. In your example the bitmap needs 10 bits, but after allowing for alignment the effect is that the heap tuple header gets 4 bytes bigger if there's any nulls. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly