From: | Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
Cc: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, Pg Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [Sender Address Forgery]Re: pg_(total_)relation_size and partitioned tables |
Date: | 2018-01-18 09:54:18 |
Message-ID: | 8ecdd7fb-02d5-4322-526a-e49e2de25064@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
v1-0001-Add-a-pg_partition_root-and-pg_partition_parent.patch | text/plain | 7.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Simon Riggs | 2018-01-18 10:08:53 | Re: [HACKERS] Surjective functional indexes |
Previous Message | Marina Polyakova | 2018-01-18 09:53:19 | Re: master make check fails on Solaris 10 |