Re: pg_(total_)relation_size and partitioned tables

On Mon, Dec 18, 2017 at 9:29 AM, Michael Paquier
<michael.paquier@gmail.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