For each varchar column - I add an extra 4 bytes For each numeric column - I add an extra 8 bytes Add a 28 bytes row overhead.
For example if i have a table with col1: integer, col2: varchar, col3 varchar, I will get: pages = (col1width + col2width + 4 + col3width + 4 + 28)* no. of rows / block size
The problem is that I have some problems for some tables where i have numeric and varchar columns. I tested on TPC-H database.
- for table t1(c_custkey, int, c_nationkey int, c_acctbal numeric) i got similar result with the real no of pages. Here c_acctbal has 8 byte, and i added the extra 8 bytes.
- for table t2(l_orderkey int, l_partkey int, l_quantiy, l_tax, l_extendedprice, l_discount) I got an error of 42 %. The last 4 columns are numeric and i added an extra 8 bytes for each of them -> 32 bytes. (colwidths + 32 + 28)*no.of rows/ block size I would have got a correct value, if i had added only 4 total bytes.. instead of the 32: (colwidths + 4 + 28)*no.of rows/ block size
I read on the documentation about some alligment bytes that can be substracted or added, so maybe that is the problem, but I don`t know how
One more question. I do not understand how to use the aligment value property. Does it depend on the position of attribute in the table? I am using Postgres 8.3
Thank you very much for any help in this regard, Cristina