Re: ToDo: show size of partitioned table

Hi

pá 30. 11. 2018 v 20:06 odesílatel Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
napsal:

>
>
> čt 29. 11. 2018 v 7:58 odesílatel Michael Paquier <michael(at)paquier(dot)xyz>
> napsal:
>
>> On Thu, Nov 22, 2018 at 03:47:11PM +0100, Pavel Stehule wrote:
>> > I have not feel well when I see in one report numbers 40 and 16, I see
>> much
>> > more comfortable when I see 24 and 16, but for this I need a different
>> > perspective
>> >
>> > What do you think about it?
>>
>> Maybe, my thought is that this would be a separate patch, and that we
>> had better keep it simple for now because that's complicated enough :)
>>
>
> any time, the behave should be consistent.
>
> I agree, so there can be another patch, that reports more about
> partitioned table for \d+
>
>
>> So I would rather have a wrapper on top of pg_partition_tree which is
>> useful for the user with matching patterns, which shows roughly:
>> - the size of the whole partition tree from the root.
>> - its direct parent if any. Potentially in the verbose output. This
>> depends on how much users have multi-level partitions. My bet would be
>> not that much, but more input from others is welcome.
>> - perhaps its level in the hierarchy, if integrated most likely as part
>> of the verbose output.
>>
>> Then with the set of commands, have a mapping close to how \d treats
>> indexes and relations:
>> - \dp shows information about partitioned tables or indexes, if no pattern
>> is defined tables show up. If a partitioned index pattern shows up,
>> then index information is displayed.
>>
>
> ok
>
> - \dpi and \dpt for respectively partitioned indexes and tables.
>>
>
new update of this patch

changes:

1. only root partitioned tables are displayed - you can see quickly total
allocated space. It is not duplicated due nested partitions.

I can imagine new additional flag - line "n" nested - and then we can
display nested partitioned tables with parent table info. Some like

\dPt - show only root partition tables
\dPnt or \dPtn - show root and nested partitioned tables

2. \dP without pattern shows root partitioned tables + total relation size.
When pattern is defined, then shows tables and indexes + table size

postgres=# \dP+
List of partitioned relations
┌────────┬────────────┬───────┬────────┬─────────────┐
│ Schema │ Name │ Owner │ Size │ Description │
╞════════╪════════════╪═══════╪════════╪═════════════╡
│ public │ parent_tab │ pavel │ 120 kB │ │
└────────┴────────────┴───────┴────────┴─────────────┘
(1 row)

postgres=# \dP+ *
List of partitioned relations or indexes
┌────────┬──────────────┬───────┬───────────────────┬────────────┬───────┬─────────────┐
│ Schema │ Name │ Owner │ Type │ Table │ Size │
Description │
╞════════╪══════════════╪═══════╪═══════════════════╪════════════╪═══════╪═════════════╡
│ public │ parent_index │ pavel │ partitioned index │ parent_tab │ 80 kB
│ │
│ public │ parent_tab │ pavel │ partitioned table │ │ 40 kB
│ │
└────────┴──────────────┴───────┴───────────────────┴────────────┴───────┴─────────────┘
(2 rows)

postgres=# \dP+ *index
List of partitioned relations or indexes
┌────────┬──────────────┬───────┬───────────────────┬────────────┬───────┬─────────────┐
│ Schema │ Name │ Owner │ Type │ Table │ Size │
Description │
╞════════╪══════════════╪═══════╪═══════════════════╪════════════╪═══════╪═════════════╡
│ public │ parent_index │ pavel │ partitioned index │ parent_tab │ 80 kB
│ │
└────────┴──────────────┴───────┴───────────────────┴────────────┴───────┴─────────────┘
(1 row)

Regards

Pavel

> --
>> Michael
>>
>