Re: [Sender Address Forgery]Re: pg_(total_)relation_size and partitioned tables

On 2018/01/02 22:45, Peter Eisentraut wrote:
> On 12/28/17 16:24, David Rowley wrote:
>>> select pg_partition_root(c.oid), c.relname, pg_table_size(c.oid)
>>> from pg_class c
>>> order by 1
>>>
>>> select pg_partition_root(c.oid), sum(pg_table_size(c.oid))
>>> from pg_class c
>>> group by 1
>>
>> That seems much nicer. I assume "root" would mean the top level
>> partitioned table. If so, would we also want
>> pg_partition_parent(regclass)? Or maybe something to control the
>> number of "levels-up" the function would run for. If we had that then
>> maybe -1 could mean "go until you find a table with no parent".
>
> Hmm, we need to think through some scenarios for what one would really
> want to do with this functionality.
>
> Clearly, the existing behavior is useful for management tasks like bloat
> and vacuum monitoring.
>
> And on the other hand you might want to have a logical view of, how big
> is this partitioned table altogether.
>
> But what are the uses for dealing with partial partition hierarchies?
> How easy do we need to make that?

I think having pg_partition_root() and pg_partition_parent() will give
users enough to get useful views as follows:

drop table p;
create table p (a int) partition by list (a);
create table p123 partition of p for values in (1, 2, 3) partition by list
(a);
create table p12 partition of p1 for values in (1, 2) partition by list (a);
create table p12 partition of p123 for values in (1, 2) partition by list (a);
create table p1 partition of p12 for values in (1);
create table p2 partition of p12 for values in (2);
create table p3 partition of p123 for values in (3);

insert into p select 1 from generate_series(1, 100);
insert into p select 2 from generate_series(1, 100);
insert into p select 3 from generate_series(1, 100);

select pg_partition_root(oid) as root_parent,
pg_partition_parent(oid) as parent,
relname as relname,
pg_total_relation_size(oid) as size
from pg_class
where relnamespace = 'public'::regnamespace
order by 4;
root_parent | parent | relname | size
-------------+--------+---------+------
p | | p | 0
p | p | p123 | 0
p | p123 | p12 | 0
p | p12 | p1 | 8192
p | p12 | p2 | 8192
p | p123 | p3 | 8192
(6 rows)

select pg_partition_root(oid) as root_parent,
sum(pg_total_relation_size(oid)) as size
from pg_class
where relnamespace = 'public'::regnamespace
group by 1
order by 1;
root_parent | size
-------------+-------
p | 24576
(1 row)

Attached a WIP patch.

Thanks,
Amit