Re: : Performance Improvement Strategy

On Wed, Oct 5, 2011 at 2:38 PM, Venkat Balaji <venkat(dot)balaji(at)verse(dot)in>wrote:

> Hello,
>
> I was attempting to calculate the actual occupied space by a Table.
>
> Below is what i did -
>
> I summed up the avg_width of each column of a table from pg_stats, which
> gives me the average size of a row (277 bytes).
>
> select* sum(avg_width) as average_row_size from pg_stats *where
> tablename='tablename'
>
> average_row_size
> ---------------------------
> 277
>
> (1 row)
>
> Calculated the actual occupied space by rows in the table as below -
>
> *Took the average_row_size * number_of_rows from pg_class*
>
> select 277*reltuples/1024 as occupied_space from pg_class where
> relname='tablename'; == 552 KB
>
> occupied_space
> -------------------------
> 552.6474609375
>
> Calculated the actual Table size (600 kb)
>
> select pg_size_pretty(pg_relation_size('tablename'));
>
>
> pg_size_pretty
> ----------------
> 600 KB
>
> (1 row)
>
> Calculated the free space with in the table (by scanning the pages - as
> suggested by Shaun Thomas) -- 14 KB
>
> SELECT pg_size_pretty(free_space) AS mb_free FROM pgstattuple('tablename');
>
> mb_free
> ---------
> 14 KB
>
> (1 row)
>
> 600 KB is the size of the table (taken through pg_size_pretty)
> 14 KB is the free space (taken through contrib modules)
> 600+14 = 586 KB -- is the occupied space by normal calculation through
> contrib modules. This is based on number of pages allocated to the table.
>

Its typo 600 - 14 = 586 KB

552 KB is the actual occupied size by the rows (taken by calculating avg row
> size ). This is based on number of rows with in the pages.
> 586-552 = 34 KB -- is still free some where with in the occupied pages (
> calculated through pg_stats and pg_class )
> 34 KB is still free within the pages ( each 8K ) which is basically taken
> as occupied space.
>
>
One more point to add to this good discussion, each row header will occupy
24 bytes + 4 bytes pointer on page to tuple.

---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/