Monitoring Performance

SELECT pg_stat_reset();

SELECT pg_stat_reset_single_table_counters(oid);

SELECT sum(heap_blks_read) as heap_blks_read, sum(heap_blks_hit) as heap_blks_hit, 
   (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as hit_ratio 
FROM pg_statio_user_tables;

SELECT sum(idx_blks_read) as idx_blks_read, sum(idx_blks_hit) as idx_blks_hit, 
   (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as hit_ratio 
FROM pg_statio_user_indexes;

SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, 
   n_live_tup rows_in_table 
FROM pg_stat_user_tables WHERE seq_scan + idx_scan > 0 ORDER BY n_live_tup DESC;

EXPLAIN ANALYZE SELECT * from my_table where filer_condition;

SELECT n.nspname as "Schema" , t.relname as "Table",c.relname as "Index"
FROM pg_catalog.pg_class c
   JOIN pg_catalog.pg_namespace n on n.oid = c.relnamespace
   JOIN pg_catalog.pg_index i on i.indexrelid = c.oid
   JOIN pg_catalog.pg_class t on i.indrelid = t.oid
WHERE c.relkind = 'i' and n.nspname not in ('pg_catalog', 'pg_toast') and 
   pg_catalog.pg_table_is_visible(c.oid)
ORDER BY n.nspname,t.relname,c.relname;

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables ORDER BY relname;

SELECT procpid, current_timestamp - xact_start AS xact_runtime, current_query
FROM pg_stat_activity where xact_start is not null 
ORDER BY xact_start;

SELECT count(*) as cnt, current_query FROM pg_stat_activity 
GROUP BY current_query 
ORDER BY cnt DESC;