Postgres: check disk space taken by materialized view?

I know how to check the size of indexes and tables in Postgres (I'm using version 9.4):

SELECT
   relname AS objectname,
   relkind AS objecttype,
   reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
   FROM pg_class
   WHERE relpages >= 8
   ORDER BY relpages DESC;

But this doesn't show materialized views. How can I check how much disk space they are taking up?

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:

• Missing "public" schema when converting a "regclass" value to text

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:

• Measure the size of a PostgreSQL table row

StackExchange Database Administrators Q#96534, answer score: 29