Blog do Seba

DBA, Consultor, Instrutor, [aprendiz de] Ninja e metido a Chef nas horas vagas!

Listando as 10 maiores tabelas no PostgreSQL

Created in Jan 22, 2013

100 Words. Read in about 1 Min.
Categories: PostgreSQL
Tags: Tips SQL

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;

Comentários