Postgres: check disk space taken by materialized view?

Question

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?

Answer 1

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

• Jack: Correct answer indeed. Note that order by size is misleading as you're printing size in human-readable form making ordering sort lexicographically
• Erwin Brandstetter: @Jack: Good point. I added a more sensible ORDER BY.