Re: : Performance Improvement Strategy

Thanks Kevin.

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

*Table Structure:*
postgres=# \d test
Table "public.test"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |

*No. of rows:*

postgres=# select relname,reltuples from pg_class where relname='test';
relname | reltuples
---------+-----------
test | 1001
(1 row)

*Average Row size:*

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

*Occupied Space:*

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

*Actual Table Size:*

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)

Its different here:

postgres=# \dt+ test
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+-------+-------------
public | test | table | postgres | 88 kB |
(1 row)

postgres=# select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty
----------------
88 kB
(1 row)

*Free Space:*

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)

*OS Level Storage:*

bash-4.1$ ll -h 16447*
-rw------- 1 postgres postgres 48K Oct 2 17:40 16447
-rw------- 1 postgres postgres 24K Oct 2 17:40 16447_fsm
-rw------- 1 postgres postgres 8.0K Oct 2 17:40 16447_vm

What has occupied in extra 8KB ?

postgres=# select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty
----------------
88 kB
(1 row)

Thanks in advance.

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