Pg Stats
main tips stats roles

Statistiche su tutti i DB

select datname, numbackends, 
              blks_read, blks_hit, 
              xact_commit, xact_rollback, deadlocks, 
              tup_returned, tup_fetched
    from pg_stat_database
    order by 1;

N.B. numbackend (connessioni attive) รจ lo snapshot non un dato cumulativo.

Dimensioni delle tabelle 'user' di un DB >= 1MB

SELECT nspname namesapce, relname, to_char((relpages::bigint * 8192 / 1048576), '999,999,999') "MB"
    FROM pg_class cl
    JOIN pg_namespace ns ON (cl.relnamespace = ns.oid)
    WHERE nspname IN ('public', 'pg_toast', 'pg_catalog')
    AND relpages >= 128
    ORDER BY relpages DESC;

Blocchi letti da tutte le user tables di un DB

select schemaname, relname, heap_blks_read from pg_statio_user_tables order by 3 desc;

Rapporto tra blocchi hit e read per le tabelle di un DB con almeno 1000 accessi

select schemaname, relname, 
        to_char(heap_blks_read/1000,'999,999,999K') as block_read,
        to_char(heap_blks_hit/1000,'999,999,999K') as block_hits, 
        to_char( 100*(1-(heap_blks_read::float/heap_blks_hit::float)), '999,999.99%') as hits_ratio 
    from pg_statio_user_tables 
    where heap_blks_read > 1000
    order by heap_blks_read desc;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License