1)Finding the table size
In bytes:
In human-readable form:
2)Findout how many tuples and pages are used by a table
1page=8kb in postgresql.
select relname as "table",
reltuples as "number of tuples",
relpages as "number of 8kb pages"
from pg_class
where relname = 'my_table';
3)Find live and dead tuples of a table
select relname as "table",
n_live_tup as "live tuples",
n_dead_tup as "dead tuples"
from pg_stat_user_tables
where relname = 'my_table';
4)Findout when a table was last vacuumed and analysed
select relname as "table",
last_vacuum as "last manual vacuum",
last_autovacuum as "last auto vacuum",
last_analyze as "last manual analyze",
last_autoanalyze as "last auto analyze"
from pg_stat_user_tables
where relname = 'my_table';
In bytes:
select relpages * 8192 as size_in_bytes from pg_class where relname = 'mytable';
(or)
postgres=# select schemaname,relname,pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables; schemaname | relname | pg_size_pretty ------------+---------+---------------- public | test1 | 72 kB (1 row) postgres=#
select pg_size_pretty(relpages * 8192) as size from pg_class where relname = 'mytable';
2)Findout how many tuples and pages are used by a table
1page=8kb in postgresql.
select relname as "table",
reltuples as "number of tuples",
relpages as "number of 8kb pages"
from pg_class
where relname = 'my_table';
3)Find live and dead tuples of a table
select relname as "table",
n_live_tup as "live tuples",
n_dead_tup as "dead tuples"
from pg_stat_user_tables
where relname = 'my_table';
4)Findout when a table was last vacuumed and analysed
select relname as "table",
last_vacuum as "last manual vacuum",
last_autovacuum as "last auto vacuum",
last_analyze as "last manual analyze",
last_autoanalyze as "last auto analyze"
from pg_stat_user_tables
where relname = 'my_table';
No comments:
Post a Comment