Subject: Re: pg_(total_)relation_size and partitioned tables
To: David Rowley <david.rowley@2ndquadrant.com>
References: <495cec7e-f8d9-7e13-4807-90dbf4eec4ea@lab.ntt.co.jp>
 <CA+TgmobG69Nf4E+VKcojXZQa9FRsbr7Dd+NORJRFMh5Umer98w@mail.gmail.com>
 <CAKJS1f9Rev615s4k7JhUsqwu-3CjR0xB2F0qpZ5fvQe+mxke0w@mail.gmail.com>
 <CAB7nPqQtNv5DLD9pXMWcT2VcsJqOnRUNBKekAapYiJxV4t=tyg@mail.gmail.com>
 <CAB7nPqTKy7HRpUM0_vMjGPkM6h6pR86Aegsw+MTmzxWr3-D-tg@mail.gmail.com>
 <1a78dea4-36b7-afb7-b3f3-1b4ff319fbb4@lab.ntt.co.jp>
 <d5323242-b571-9317-1e5b-ee5fd96c2f4a@2ndquadrant.com>
 <CAKJS1f86CjuW7-6QaXKPZLtcVCMo=ocprNdSk+dcvbpPyXMxtQ@mail.gmail.com>
Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>,
 Michael Paquier <michael.paquier@gmail.com>,
 Robert Haas <robertmhaas@gmail.com>,
 Pg Hackers <pgsql-hackers@postgresql.org>
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <93bef1b2-2ed0-c257-158e-efdf5b0ff9dd@2ndquadrant.com>
Date: Tue, 2 Jan 2018 08:45:34 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:52.0)
 Gecko/20100101 Thunderbird/52.5.2
MIME-Version: 1.0
In-Reply-To: 
 <CAKJS1f86CjuW7-6QaXKPZLtcVCMo=ocprNdSk+dcvbpPyXMxtQ@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

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?

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services