Last active
February 6, 2018 00:46
-
-
Save gmr/f69e10cc8dcd8e34df755201a9ef988a to your computer and use it in GitHub Desktop.
A PostgreSQL view that combines the relation size, the size of its indexes, the size of its toast table, and the size of the toast table indexes, combines them to give you a total table size.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE VIEW public.table_sizes AS | |
WITH tables AS ( | |
SELECT a.oid, b.nspname, a.relname, a.reltoastrelid, pg_relation_size(a.oid) AS size | |
FROM pg_class AS a | |
JOIN pg_namespace AS b ON b.oid = a.relnamespace | |
WHERE a.relkind = 'r' | |
AND b.nspname NOT IN ('pg_catalog', 'information_schema')), | |
indexes AS ( | |
SELECT i.oid, n.nspname, i.relname AS idxname, c.oid AS reloid, c.relname AS relname, | |
pg_relation_size(i.oid) AS size | |
FROM pg_index AS x | |
JOIN pg_class AS c ON c.oid = x.indrelid | |
JOIN pg_class AS i ON i.oid = x.indexrelid | |
JOIN pg_namespace AS n ON n.oid = c.relnamespace | |
WHERE c.relkind = 'r'::char | |
AND i.relkind = 'i'::char), | |
toast AS ( | |
SELECT a.oid, b.nspname, a.relname, pg_relation_size(a.oid) AS size | |
FROM pg_class AS a | |
JOIN pg_namespace AS b ON b.oid = a.relnamespace | |
WHERE a.relkind = 't'), | |
toast_indexes AS ( | |
SELECT i.oid, n.nspname, i.relname AS idxname, c.oid AS reloid, c.relname AS relname, | |
pg_relation_size(i.oid) AS size | |
FROM pg_index AS x | |
JOIN pg_class AS c ON c.oid = x.indrelid | |
JOIN pg_class AS i ON i.oid = x.indexrelid | |
JOIN pg_namespace AS n ON n.oid = c.relnamespace | |
WHERE c.relkind = 't'::char | |
AND i.relkind = 'i'::char), | |
totals AS ( | |
SELECT a.nspname, a.relname, | |
a.size AS table_size, | |
CASE WHEN sum(b.size) IS NULL THEN 0 ELSE sum(b.size) END AS index_size, | |
CASE WHEN sum(c.size) IS NULL THEN 0 ELSE sum(c.size) END AS toast_size, | |
CASE WHEN sum(d.size) IS NULL THEN 0 ELSE sum(d.size) END AS toast_index_size | |
FROM tables AS a | |
LEFT JOIN indexes AS b ON b.reloid = a.oid | |
LEFT JOIN toast AS c ON c.oid = a.reltoastrelid | |
LEFT JOIN toast_indexes AS d ON d.reloid = c.oid | |
GROUP BY a.nspname, a.relname, a.size) | |
SELECT nspname AS namespace, | |
relname AS table, | |
pg_size_pretty(table_size) AS table_size, | |
pg_size_pretty(index_size) AS index_size, | |
pg_size_pretty(toast_size) AS toast_size, | |
pg_size_pretty(toast_index_size) AS toast_index_size, | |
pg_size_pretty(table_size + index_size + toast_size + toast_index_size) AS total_size | |
FROM totals | |
ORDER BY (table_size + index_size + toast_size + toast_index_size) DESC; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment