Skip to content

Instantly share code, notes, and snippets.

@gmr
Last active February 6, 2018 00:46
Show Gist options
  • Save gmr/f69e10cc8dcd8e34df755201a9ef988a to your computer and use it in GitHub Desktop.
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.
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