Postgresql – Postgres: check disk space taken by materialized view

database-sizedisk-spacematerialized-viewpostgresql

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?

Best Answer

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 relname   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;

Where the available types 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

Related Solutions

PostgreSQL – Refresh Materialized View Locks

REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock, so SELECTs may still run. Per the manual.

There seems to be an oversight with REFRESH MATERIALIZED VIEW (without CONCURRENTLY) as it's not listed there. A search of the source code shows that ExecRefreshMatView take an ACCESS EXCLUSIVE lock, as you might expect, so no other queries may run on the view.

I've submitted a docs patch to list it.

PostgreSQL Performance – Bad Performance with SELECT on View Checking IS NULL

This is more a hunch as I don't know the optimizer details and possible rewritings but FULL JOIN is quite restricting.

So, you could rewrite the view by splitting the FULL JOIN to a UNION ALL of 2 joins:

SELECT ... 
FROM ao LEFT JOIN ms 
    ON ao.ao_id = ms.ms_ao_id 

UNION ALL 

SELECT ... 
FROM ao RIGHT JOIN ms 
    ON ao.ao_id = ms.ms_ao_id 
WHERE ao.ao_id IS NULL ;

This might help the optimizer to identify that for the condition (where ao_ms_id is null) the nulls can be produced in 2 different ways, one from the table itself and the other as a by-product of the outer join. So the condition can be pushed down for the first part of the union and removed completely for the second part.

If that doesn't work, you could "simplify" the count(*) query yourself:

SELECT 
  ( SELECT COUNT(*)
    FROM ao LEFT JOIN ms 
        ON ao.ao_id = ms.ms_ao_id 
    WHERE ao.ao_ms_id IS NULL
  )
    +
  ( SELECT COUNT(*)
    FROM ao RIGHT JOIN ms 
        ON ao.ao_id = ms.ms_ao_id 
    WHERE ao.ao_id IS NULL 
  --  AND ao.ao_ms_id IS NULL           -- removed
  ) ;

Best Answer

Related Solutions

PostgreSQL – Refresh Materialized View Locks

PostgreSQL Performance – Bad Performance with SELECT on View Checking IS NULL

Related Question