Páginas

terça-feira, 30 de junho de 2015

[PostgreSQL] - Listando as 10 maiores tabelas no PostgreSQL

Para listar as maiores tabelas do seu banco de dados, utilize a consulta abaixo:

WITH table_stats AS (
  SELECT
    schemaname,
    tablename,
    pg_relation_size(schemaname || '.'|| tablename) as table_size,
    (pg_total_relation_size(schemaname || '.'|| tablename) - pg_relation_size(schemaname || '.'|| tablename)) as index_size,
    pg_total_relation_size(schemaname || '.'|| tablename) as total_size
  FROM
    pg_tables
)
SELECT
  table_stats.schemaname,
  table_stats.tablename,
  pg_size_pretty(table_stats.table_size) as table_size,
  pg_size_pretty(table_stats.index_size) as index_size,
  pg_size_pretty(table_stats.total_size) as total_size
FROM
  table_stats

WHERE
  -- ajuste o filtro conforme sua necessidade!
  table_stats.schemaname = 'public'
ORDER BY
  table_stats.total_size desc,
  table_stats.index_size desc,
  table_stats.table_size desc
LIMIT 10;
Fonte: http://swebber.me/blog/2013/01/listando-as-10-maiores-tabelas-no-postgresql/

Nenhum comentário:

Postar um comentário