19

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?

1 Answer 1

28

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:

3
  • 2
    Correct answer indeed. Note that order by size is misleading as you're printing size in human-readable form making ordering sort lexicographically
    – Jack
    Nov 9, 2016 at 11:23
  • @Jack: Good point. I added a more sensible ORDER BY. Nov 9, 2016 at 15:45
  • Thanks @Nick for pointing out the ambiguity with the schema. I updated with a simpler version. Apr 6, 2022 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.