Dicas PostgreSQL

Para verificar o tamanho das tabelas do banco de dados no PostgreSQL podemos utilizar o comando abaixo:

SELECT esquema, tabela,
pg_size_pretty(pg_relation_size(esq_tab)) AS tamanho,
pg_size_pretty(pg_total_relation_size(esq_tab)) AS tamanho_total
FROM (SELECT tablename AS tabela,
schemaname AS esquema,
schemaname||'.'||tablename AS esq_tab
FROM pg_catalog.pg_tables
WHERE schemaname NOT
IN ('pg_catalog', 'information_schema', 'pg_toast') ) AS x
ORDER BY pg_total_relation_size(esq_tab) DESC;

Para cancelar instruções SQL podemos utilizar o comando abaixo:

SELECT
pg_terminate_backend(procpid)
FROM
pg_stat_activity
WHERE
current_query = '<IDLE>'

Para verificar índices que estão faltando podemos utilizar a consulta abaixo:

SELECT 
 relname AS TableName
 ,seq_scan-idx_scan AS TotalSeqScan
 ,CASE WHEN seq_scan-idx_scan > 0 
 THEN 'Missing Index Found' 
 ELSE 'Missing Index Not Found' 
 END AS MissingIndex
 ,pg_size_pretty(pg_relation_size(relname::regclass)) AS TableSize
 ,idx_scan AS TotalIndexScan
FROM pg_stat_all_tables
WHERE schemaname='public'
 AND pg_relation_size(relname::regclass)>100000 
ORDER BY 2 DESC;

Para visualizar as queries que estão rodando a mais de 1 minuto podemos usar a consulta abaixo:

SELECT
CURRENT_TIMESTAMP - query_start AS runtime,
datname,
usename,
query
FROM
pg_stat_activity
WHERE
state = 'active'
AND CURRENT_TIMESTAMP - query_start > '1 min'
ORDER BY
1 DESC;