Problem with estimating pages for a table

From: Cristina M <cristina(dot)maier(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with estimating pages for a table
Date: 2009-05-11 14:31:19
Message-ID: 421531.20818.qm@web56802.mail.re3.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

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

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

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2009-05-11 16:25:25 Re: Btree indizes, FILLFACTOR, vacuum_freeze_min_age and CLUSTER
Previous Message esemba 2009-05-11 13:04:39 [fulltext]Gin index full scan