Tuesday, February 12, 2019

Top 5 biggest tables in currently active scheme in PostgreSQL

SELECT C.relname                                     AS table_name,
       pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size,
       S.n_live_tup                                  AS total_rows
FROM pg_class C
       LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
       LEFT JOIN pg_stat_user_tables S ON (C.relname = S.relname)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  AND C.relkind <> 'i'
  AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.