(NOTES) Menganalisis Performance Posgresql

Ini catatan berupa Query yang untuk menganalisis performa database Postgresql


## LONGEST TABLES


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 TABLES

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 READ TABLES

select schemaname || '.' || relname as table, heap_blks_read as disk_reads,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 15;



# MOST WRITTEN TABLES
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;