Re: ToDo: show size of partitioned table

On 2018/06/20 16:21, Pavel Stehule wrote:
> 2018-06-20 7:44 GMT+02:00 Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>:
>
>> On 2018/06/02 0:15, Ashutosh Bapat wrote:
>>> I think we should at least display "Type" as "partitioned table" for a
>>> partitioned table, so that it's easy to understand why the size is 0;
>>> partitioned tables do not hold any data by themselves.
>>
>> There was a long discussion last year (during PG 10 beta period), such as
>> [1], and it seems most of us agreed to doing the above. Maybe, we should
>> finally do it for PG 12, if not PG 11.
>>
>> Regarding showing the size of partitioned tables, there are many opinions
>> and it's not clear if showing it in \dt itself is appropriate. For one,
>> there is no pg_relation_size() or pg_table_size() equivalent in the
>> backend for aggregating the size of all tables in a partition tree and I
>> think people are not quite on board about having such a function in the
>> backend [2].
>
> Now, the number of partitions can be low, but if the Postgres can better
> process high number of partitions, then for some tables we can have
> hundreds partitions.
>
> Then usual \dt can be not too much usable. The aggregation can be done on
> client side. But maybe this idea is premature. Now, for PG 12, we can start
> with
>
> \dtP+ command for showing partition tables only with aggregate size via all
> related partitions.
>
> Is it acceptable idea?

Do you mean \dt continues to show size 0 for partitioned tables, but with
the new option (\dtP+) shows the actual size by aggregating across
partitions? +1 to such a feature, but we need to agree on an acceptable
implementation for that. How does the aggregation happen:

1. In a new dedicated function in the backend (parallel to pg_table_size)?

or

2. psql issues a separate query to compute the total size of a partition
tree

For option 2, I had posted a patch that simplifies writing such a query
and posted that here:

https://www.postgresql.org/message-id/7a9c5328-5328-52a3-2a3d-bf1434b4dd1d%40lab.ntt.co.jp

With that patch, the query to get the total size of a partition tree
becomes as simple as:

select sum(pg_table_size(p)) as size
from pg_get_inheritance_tables('partitioned_table_name') p

Thanks,
Amit