--
-- Amount of space per object used after vacuum
--
\echo
\echo 'Caution: This skript does only print usefull information'
\echo '         if you run VACUUM before!'
\echo
--VACUUM;
SELECT c1.relname AS "tablename", c2.relname AS "indexname",
       c2.relpages * 8 AS "size_kb", c2.relfilenode AS "filename"
FROM pg_class c1, pg_class c2, pg_index i
WHERE c1.oid = i.indrelid
  AND i.indexrelid = c2.oid
UNION
SELECT relname, NULL, relpages * 8, relfilenode
FROM pg_class
WHERE relkind = 'r'
ORDER BY tablename, indexname DESC, size_kb;