Re: pg_(total_)relation_size and partitioned tables

On Mon, Dec 18, 2017 at 9:29 AM, Michael Paquier
<michael(dot)paquier(at)gmail(dot)com> wrote:
> The barrier here is thin. What's proposed here is already doable with
> a WITH RECURSIVE query. So why not just documenting this query and be
> done with it instead of complicating the code? It seems to me that the
> performance in calling pg_relation_size() in a cascading times fashion
> would not matter much. Or one could invent an additional cascading
> option which scans inheritance and/or partition chains, or simply have
> a new function.

I just blogged on the matter, and here is one possibility here
compatible with v10:
WITH RECURSIVE partition_info
(relid,
relname,
relsize,
relispartition,
relkind) AS (
SELECT oid AS relid,
relname,
pg_relation_size(oid) AS relsize,
relispartition,
relkind
FROM pg_catalog.pg_class
WHERE relname = 'parent_name' AND
relkind = 'p'
UNION ALL
SELECT
c.oid AS relid,
c.relname AS relname,
pg_relation_size(c.oid) AS relsize,
c.relispartition AS relispartition,
c.relkind AS relkind
FROM partition_info AS p,
pg_catalog.pg_inherits AS i,
pg_catalog.pg_class AS c
WHERE p.relid = i.inhparent AND
c.oid = i.inhrelid AND
c.relispartition
)
SELECT * FROM partition_info;

This is not really straight-forward. You could as well have the
pg_relation_size call in the outer query.
--
Michael