From: | Raghavendra <raghavendra(dot)rao(at)enterprisedb(dot)com> |
---|---|
To: | Venkat Balaji <venkat(dot)balaji(at)verse(dot)in> |
Cc: | sthomas(at)peak6(dot)com, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PGSQL Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: : Performance Improvement Strategy |
Date: | 2011-10-05 09:30:59 |
Message-ID: | CA+h6AhhuQMoa61bO7K0P-PdnAOcxkZ2SddT5DYGrA4w9mqQ2sQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Cottenceau | 2011-10-05 09:39:58 | Re: : Performance Improvement Strategy |
Previous Message | Venkat Balaji | 2011-10-05 09:08:43 | Re: : Performance Improvement Strategy |