Обсуждение: Problem with estimating pages for a table

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/

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

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

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