Postgres Pro
Downloads
Home   >   mailing lists

Thread: Problem with estimating pages for a table

Problem with estimating pages for a table

From
Cristina M
Date:
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

Re: Problem with estimating pages for a table

From
Euler Taveira de Oliveira
Date:
Cristina M escreveu:
> - for table t2(l_orderkey int, l_partkey int, l_quantiy, l_tax,
> l_extendedprice, l_discount) I got an error of 42 %.
> 
I suspect you have NULLs in your table; they're stored as bitmaps, so they use
little space.


--  Euler Taveira de Oliveira http://www.timbira.com/

Re: Problem with estimating pages for a table

From
Alvaro Herrera
Date:
Cristina M wrote:

> 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

Keep in mind that if you have varchar(1000) and store 30 bytes of text,
it will use 30+4, not 1000+4.

Very long attributes may be compressed and/or stored in a side table
called the TOAST table; only a pointer to it remains on the base table
(which is some 20 bytes long I think).

Also keep in mind that there's a lot of space lost to alignment
considerations, so don't expect things to match down to the last byte.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: Problem with estimating pages for a table

From
Robert Haas
Date:
On Wed, May 13, 2009 at 6:08 AM, Cristina M <cristina.maier@yahoo.com> wrote:
> 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 :

You haven't given us a lot of information on what you want to do with
this, but if by any chance it's helpful to get the actual number of
pages for some particular table, you can do it like this:

select relpages from pg_class where oid = 'name_of_the_table'::regclass;

There is also a handy function pg_relation_size().

...Robert

Re: Problem with estimating pages for a table

From
Dimitri Fontaine
Date:
Hi,

Cristina M <cristina.maier@yahoo.com> writes:
> 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                                                                                 

You may find Greg Startk's presentation on the topic helpful:
http://wiki.postgresql.org/wiki/Image:How_Long_Is_a_String.pdf

Regards,
-- 
dim