Re: ToDo: show size of partitioned table

čt 7. 2. 2019 v 16:03 odesílatel Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
napsal:

> On 2019-Feb-07, Pavel Stehule wrote:
>
> > Your example
> >
> > postgres=# \dPtn+
> > List of partitioned tables
> > ┌────────┬────────┬───────┬─────────────┬────────────┬─────────────┐
> > │ Schema │ Name │ Owner │ Parent name │ Size │ Description │
> > ╞════════╪════════╪═══════╪═════════════╪════════════╪═════════════╡
> > │ public │ p │ pavel │ │ 8192 bytes │ │
> > │ public │ p_1 │ pavel │ p │ 8192 bytes │ │
> > │ public │ p_1_bc │ pavel │ p_1 │ 8192 bytes │ │
> > └────────┴────────┴───────┴─────────────┴────────────┴─────────────┘
> > (3 rows)
> >
> > I hope so the interpretation is clean .. there are three partitioned
> tables
> > (two are subpartitioned tables). Any partitioned table has assigned 8KB
> of
> > data.
> >
> > We can introduce new column "size with sub partitions" where these
> numbers
> > can be counted together. But for term "size" I expect valid rule S1+S2+..
> > SN = total size.
>
> Right now, a partitioned table has no size of its own; its only storage
> is in its children. But that might change in the future, for example if
> we implement global indexes. Then it will be useful to show these sizes
> separately.
>
> I suggest that we should have one column for the aggregated size of its
> children, and another column for the "local" size. So currently the
> local size would always be zero for partitioned tables. The other
> column (maybe "Children Size") would be the sum of the sizes of all its
> partitions.
>
> So I think this should be:
>

So this is third proposals :-)

Can I recapitulate it?

1. My proposal - the "size" = sum(partitions on level1), "size with nested
partitioned tables" = sum(partitions on all levels)
2. Amit's proposal - the "size" = sum(partions on all levels)
3. Alvaro's proposal - the "size" = empty now, "children size" =
sum(partitions on level1)

Every proposal is valid, and has some sense and display some information
from some perspective.

So first we should not to use one word term "size" in this context, because
there is any agreement.

Can we find some terminology for two *sizes*?

1. size of all partitions with nesting level = 1
2. size of all partitions without nesting level check.

maybe there can be third variant - size of all partitions with nesting
level > 1

I am not sure so "children size" is good term because this structure is
tree, and the deep is not specified.

Maybe "Immediate partitions size" for @1, "total partitions size" for @2,
and "indirect partitions size" for @3.

Then we can display @1, @2 or @1 or @3.

I hope so I mentioned all possible variants.

Comments, notes?

Pavel

>
> List of partitioned tables
>
> ┌────────┬────────┬───────┬─────────────┬────────────┬───────────────┬────────────
> │ Schema │ Name │ Owner │ Parent name │ Size │ Children Size │
> Description
> ╞════════╪════════╪═══════╪═════════════╪════════════╪═══════════════╡
> │ public │ p │ pavel │ │ │ 8192 bytes │
> │ public │ p_1 │ pavel │ p │ │ 8192 bytes │
> │ public │ p_1_bc │ pavel │ p_1 │ 8192 bytes │ │
>
> └────────┴────────┴───────┴─────────────┴────────────┴───────────────┴─────────────
>
>
>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>