Re: : Performance Improvement Strategy

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.

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.