PostgreSQL Odoo Table Analysis

-- biggest table record
SELECT schemaname || '.' || relname as table,n_live_tup as num_rows
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC LIMIT 10;

-- biggest table size
SELECT nspname|| '.' || relname as table, 
pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC LIMIT 10;

-- most accessed table
SELECT schemaname || '.' || relname as table, 
heap_blks_read as disk_read, heap_blks_hit as cache_reads, 
heap_blks_read + heap_blks_hit as total_reads
FROM pg_statio_user_tables
ORDER BY heap_blks_read + heap_blks_hit DESC LIMIT 10;


-- most write table
SELECT schemaname || '.' || relname as table, 
seq_scan, idx_scan, idx_tup_fetch + seq_tup_read lines_read_total,
n_tup_ins as num_insert, n_tup_upd as num_update, n_tup_del as num_delete
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC LIMIT 10;

-- locking
create view waiter_holder as 
     select wait_act.datname, wait_act.usename, waiter.pid as wpid, holder.pid as hpid, waiter.locktype as type, 
     waiter.transactionid as xid, waiter.virtualtransaction as wvxid, 
     holder.virtualtransaction as hvxid, waiter.mode as wmode, holder.mode as hmode, 
     wait_act.state as wstate,hold_act.state as hstate, pg_class.relname, 
     substr(wait_act.query,1,30) as wquery, substr(hold_act.query,1,30) as hquery, 
     age(now(),wait_act.query_start) as wdur, age(now(),hold_act.query_start) as hdur 
from pg_locks holder join pg_locks waiter on 
     ( holder.locktype = waiter.locktype 
     and ( holder.database, holder.relation, holder.page, holder.tuple, holder.virtualxid, 
     holder.transactionid, holder.classid, holder.objid, holder.objsubid ) is not 
     distinct from ( 
         waiter.database, waiter.relation, waiter.page, waiter.tuple, waiter.virtualxid, 
         waiter.transactionid, waiter.classid, waiter.objid, waiter.objsubid )) 
join pg_stat_activity hold_act on (holder.pid=hold_act.pid) 
join pg_stat_activity wait_act on (waiter.pid=wait_act.pid) 
left join pg_class on (holder.relation = pg_class.oid) 
where holder.granted and not waiter.granted 
order by wdur desc; 

SELECT * from waiter_holder;