postgresq.sql

-- a partir de quando as statistics estão sendo coletadas and else
-- https://www.postgresql.org/docs/9.2/static/monitoring-stats.html#PG-STAT-DATABASE-VIEW
select stats_reset, datname, to_char(tup_returned*100.0 / (tup_fetched + tup_returned), '90.00%') as pct_tup_returned, 
tup_fetched, tup_returned, temp_bytes, tup_inserted, tup_updated, tup_deleted, 
xact_commit + xact_rollback as total_transaction, xact_commit, xact_rollback, deadlocks
from pg_stat_database
where datname not like 'template%';


-- statistics about columns
select attname, n_distinct, null_frac, most_common_vals, correlation, most_common_freqs
from pg_stats 
where tablename = 'tabela' and schemaname = 'schema' and attname = 'coluna';


-- statistics about table
select *
from pg_catalog.pg_stat_all_tables
where schemaname = 'schema' and relname = 'tabela';


-- fonte: https://www.slideshare.net/matheus_de_oliveira/estatisticas-postgre-sqlpgdaycps2014

-- proporção de read em cache em relação a read em disco + read em cache
-- blks_hit -> lido em memória
-- blks_read -> lido em disco
select sum(blks_hit) / sum((blks_read + blks_hit)::numeric)
from pg_stat_database
where blks_read + blks_hit <> 0;

-- taxa de escrita de buffers por checkpoint vs pg_stat_bgwriter > procura-se valores próximos de 1
-- próximo a zero indica necessidade de forçar mais checkpoints ou aumentar shared_buffers
-- buffers_checkpoint -> escrita da memória pro disco feita por checkpoint
-- buffers_backend -> escrita da memória pro disco feita por backend, necessidade de espaço nos buffers
select buffers_checkpoint / (buffers_checkpoint + buffers_backend)::numeric as checkpoint_ratio
from pg_stat_bgwriter;

-- requisições de checkpoint pelo pg_stat_bgwriter > procura-se valores próximos de 1
-- valores próximos de 0 indica necessidade de aumentar checkpoint_segments ou reduzir checkpoint_timeout
-- checkpoint gera concorrência de disco!
-- checkpoints_timed -> checkpoint rodou por time
-- checkpoints_req -> checkpoint rodou por falta de espaço
select checkpoints_timed / (checkpoints_timed + checkpoints_req)::numeric as timed_ratio
from pg_stat_bgwriter;

-- utilização de arquivos temporários
-- quanto mais alto mais indica aumentar work_mem
select pg_size_pretty(sum(temp_bytes)) as size
from pg_stat_database;
show work_mem;


-- pg_stat_user_tables
-- care: tabelas pequenas n tem problema seq_scan
select put.schemaname, relname, reltuples::integer as estimated_rows, 
idx_scan, seq_scan, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_tup_del,
n_dead_tup, n_live_tup, n_dead_tup::numeric / (n_dead_tup + n_live_tup + 1) as pct_dead
from pg_stat_user_tables  put
inner join pg_class pc using (relname)
-- reltuples
--order by reltuples::integer desc;
-- seq scan x idx_scan
--order by (seq_scan - coalesce(idx_scan, 0)) desc;
-- n_tup_upd x n_tup_hot_upd
--order by n_tup_upd - n_tup_hot_upd desc;
-- dead tuples relativo
--order by n_dead_tup::numeric / (n_dead_tup + n_live_tup + 1) desc;
-- dead tuples absoluto
order by n_dead_tup desc;


-- índices não utilizados
--    fonte: https://jmorano.moretrix.com/2014/02/postgresql-monitor-unused-indexes/
SELECT 
    relid::regclass AS table, 
    indexrelid::regclass AS index, 
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size 
FROM 
    pg_stat_user_indexes 
WHERE 
    idx_scan = 0 
order by pg_relation_size(indexrelid::regclass) desc;

-- size of all unused indexes
SELECT pg_size_pretty(sum(pg_relation_size(indexrelid::regclass))) AS index_size 
FROM 
    pg_stat_user_indexes 
WHERE 
    idx_scan = 0;
   
-- quantidade de indexes por tabela
select relid::regclass as table, count(*), pg_size_pretty(sum(pg_relation_size(indexrelid::regclass)))
from pg_stat_user_indexes
group by 1
order by count(*) desc;

select idx.indexrelname, pg_size_pretty(pg_relation_size(indexrelid::regclass))
from pg_stat_user_indexes idx   
where schemaname = 'comum' and relname = 'unidade';


-- indexes duplicados
select 
    indrelid::regclass, array_agg(indexrelid::regclass) 
from 
    pg_index 
group by 
    indrelid, indkey 
having 
    count(*) > 1;
    
-- indexes e suas chaves
with index_com_position as (
    select *, unnest(indkey) as column_position
    from pg_index
)
select 
    icp.indrelid::regclass, 
    indexrelid::regclass, 
    array_agg(c.column_name::text), 
    pg_size_pretty(pg_relation_size(indexrelid))
from index_com_position icp 
inner join information_schema.columns c on (icp.indrelid::regclass::text = format('%s.%s', c.table_schema, c.table_name) and icp.column_position = c.ordinal_position)
group by 1, 2
order by count(*) desc;


    
-- table metrics
select schemaname, relname, n_live_tup, idx_scan, seq_scan, n_tup_ins, n_tup_upd, n_tup_hot_upd, n_tup_del
from pg_stat_user_tables;


-- total number of locks
select count(*)
from pg_locks;


SELECT sa.* FROM pg_catalog.pg_stat_activity sa
order by query;


SELECT blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(blockingl.transactionid=blockedl.transactionid
  AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted;


-- active connections
select datname, numbackends, numbackends::float / m.max_conn as proporcao, m.max_conn
from pg_stat_database, (select current_setting('max_connections')::integer) as m(max_conn)
where datname not like 'template%';



-- tamanho de objetos
select
   n.nspname as schema, 
   relname AS objectname,
   relkind AS objecttype,
   reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
   FROM pg_class
   JOIN pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace
   WHERE relpages >= 8
   ORDER BY relpages DESC;
   
   
   
SELECT nspname, relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c INNER JOIN pg_namespace n ON (c.relnamespace = n.oid) WHERE relkind = 'r' ORDER BY pg_total_relation_size(c.oid) DESC NULLS last;
  
   
   
-- tamanho por tipo de object
select 
case relkind 
    when 'r' then 'ordinary table'
    when 'i' then 'index'
    when 'S' then 'sequence'
    when 'v' then 'view'
    when 'm' then 'materialized view'
    when 'c' then 'composite type'
    when 't' then 'TOAST table'
    when 'f' then 'foreign table'
end as tipo
, pg_size_pretty(sum(relpages::bigint*8*1024))
from pg_class
group by 1
order by sum(relpages::bigint*8*1024) desc;
   


-- exemplo de consulta sobre tamanho de colunas em relação ao total da tabela
select
    pg_size_pretty(sum(pg_column_size(senha_nt))) as total_size,
    avg(pg_column_size(senha_nt)) as average_size,
    sum(pg_column_size(senha_nt)) * 100.0 / pg_relation_size('comum.usuario') as percentage
from comum.usuario;


-- fonte: https://www.youtube.com/watch?v=Y5l4yLNbC2Y
--    tabelas com dados normalmente pegos em cache, no lugar do disco
select relid::regclass, heap_blks_hit, heap_blks_read,
(heap_blks_hit::numeric/(heap_blks_read + heap_blks_hit + 1)) as pct
from pg_statio_all_tables
where heap_blks_hit + heap_blks_read > 0
order by 4 asc;


-- # necessário ver com o usuário postgres
select *
from pg_stat_user_functions;


-- conflicts
select *
from pg_stat_database_conflicts;


-- waiting queries
select * from pg_stat_activity where waiting = 't';



-- top 3 longest running queries
select datname, usename, now() - query_start as query_runtime, *
from pg_stat_activity
order by 3 desc limit 3;



-- sizes of all databases
select datname, pg_size_pretty(pg_database_size(datname))
from pg_database;


-- indexes duplicados

select n_table.nspname, c_table.relname, i.indkey[0:p.i] as prefixo, string_agg(pg_get_indexdef(c_index.oid), E'\n')
from pg_catalog.pg_index i 
inner join pg_catalog.pg_class c_index on c_index.oid = i.indexrelid
inner join pg_catalog.pg_class c_table on c_table.oid = i.indrelid
inner join pg_catalog.pg_namespace n_table on n_table.oid = c_table.relnamespace
inner join (values (0), (1), (2), (3), (4), (5)) as p(i) on i.indkey[p.i] is not null
where n_table.nspname not in ('information_schema', 'pg_catalog')
group by 1, 2, 3
having count(*) > 1
order by 1, 2, 3;


SELECT schemaname AS schema, relname AS table, 
a.indexrelid::regclass AS index1, 
b.indexrelid::regclass AS index2, pg_get_indexdef(a.indexrelid) AS indexdef1, 
pg_get_indexdef(b.indexrelid) AS indexdef2 
FROM pg_stat_user_indexes s 
INNER JOIN pg_index a ON (s.indexrelid = a.indexrelid) 
INNER JOIN pg_index b ON (a.indrelid = b.indrelid) 
WHERE 
    a.indkey <> b.indkey 
    AND a.indisvalid 
    AND b.indisvalid 
    AND substring(a.indkey::text || ' ' from '^' || b.indkey::text || ' ') IS NOT NULL;