postgresql: table size

Couple ways, I would do to find out about the table size of a table.

\o table.txt

\d+
cat table.txt |grep vpx_event

OR through pg_class and pg_namespace
##by size

SELECT nspname || '.' || relname AS "relation", relkind,
    pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
    AND C.relkind <> 'i'
    AND relname like '%vpx_event%'
ORDER BY pg_total_relation_size(C.oid) DESC;


##by rows

SELECT table_name
,pg_relation_size(table_name) as size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema',
'pg_catalog')
ORDER BY size DESC
LIMIT 10;


#look at table and size in a specific schema.

select relname, pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) 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 LIMIT 100;

No comments:

Post a Comment