DB stats query

console.sql

-- Table Statistics
SELECT
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_all_tables
ORDER BY relname;


-- Index Statistics
SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_all_indexes
ORDER BY idx_scan DESC;

-- Database Connection Statistics
SELECT * FROM pg_stat_activity;

-- Database Transaction Statistics
SELECT
    datname,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit
FROM pg_stat_database;

-- I/O Statistics
SELECT
    relname AS table_name,
    heap_blks_read,
    heap_blks_hit,
    idx_blks_read,
    idx_blks_hit
FROM pg_statio_all_tables;

--  Check Locks
SELECT * FROM pg_locks;

-- Disk Usage for All Tables
SELECT
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;