This is assuming that materialized views have relpages >= 8
in pg_class
, which doesn't have to be the case. It can actually be empty - not populated yet, indicated by pg_class.relispopulated = FALSE
. The corresponding disk file has a zero size in this case.
Try instead:
SELECT oid::regclass::text AS objectname
, relkind AS objecttype
, reltuples AS entries
, pg_size_pretty(pg_table_size(oid)) AS size -- depending - see below
FROM pg_class
WHERE relkind IN ('r', 'i', 'm')
ORDER BY pg_table_size(oid) DESC;
The cast to regclass
makes sure that the name of the relation is quoted and schema-qualified where needed (where the current search_path
would not resolve to the same object). See:
Available values for relkind
are:
r
= ordinary table,
i
= index,
S
= sequence,
v
= view,
m
= materialized view,
c
= composite type,
t
= TOAST table,
f
= foreign table
Use one of the database object size functions rather than building your own. Be aware that the "size of a table" can be defined in different ways. Details: