Re: : Performance Improvement Strategy

Thanks Kevin. 

Am in 9.1 and tested same scenario, how exactly storage metrics are calculated. Please comment.

postgres=# \d test

     Table "public.test"

 Column |  Type   | Modifiers

--------+---------+-----------

 id     | integer |

 name   | text    |

postgres=# select relname,reltuples from pg_class where relname='test';

 relname | reltuples

---------+-----------

 test    |      1001

(1 row)

postgres=# select sum(avg_width) as average_row_size from pg_stats where tablename='test';

 average_row_size

------------------

               17

(1 row)

postgres=# select 17*reltuples/1024 as "No.of.Row_size * No.of.Rows = Occupied_Space" from pg_class where relname='test';

 No.of.Row_size * No.of.Rows = Occupied_Space

----------------------------------------------

                                16.6181640625

postgres=# select pg_size_pretty(pg_relation_size('test'));

 pg_size_pretty

----------------

 48 kB

(1 row)

or 

postgres=# SELECT relname, reltuples, pg_size_pretty(relpages*8*1024) as size FROM pg_class, pg_namespace WHERE pg_namespace.oid = pg_class.relnamespace AND relkind = 'r' AND nspname = 'public' ORDER BY relpages DESC;

 relname | reltuples | size

---------+-----------+-------

 test    |      1001 | 48 kB

(1 row)

postgres=# SELECT pg_size_pretty(free_space) AS mb_free FROM pgstattuple('test');

  mb_free

-----------

 936 bytes

(1 row)

or

postgres=# select * from pgstattuple('test');

 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent

-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------

     49152 |        1001 |     41041 |          83.5 |                0 |              0 |                  0 |        936 |          1.9

(1 row)