Hello,
I posted to the general list, and didn't receive any replies. Therefore, I am trying this list now, hopefully this is the right mailing list for this type of questions.
I am trying to compute the no of pages of a table. I am using the formula :
pages = ( columns width + 28) * no. of rows / block size
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
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 |
