Re: ToDo: show size of partitioned table

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
Cc: Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Kyotaro HORIGUCHI <horiguchi(dot)kyotaro(at)lab(dot)ntt(dot)co(dot)jp>, Justin Pryzby <pryzby(at)telsasoft(dot)com>, Michael Paquier <michael(at)paquier(dot)xyz>, mathias(at)brossard(dot)org, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: ToDo: show size of partitioned table
Date: 2019-04-06 19:24:36
Message-ID: CAFj8pRCSa6J3aqcAKAsGzw7gUnQkw=04PD=Ddit_HmcHG0ynVQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

so 6. 4. 2019 v 6:36 odesílatel Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
napsal:

> On 2019-Apr-05, Alvaro Herrera wrote:
>
> > Looking at the current proposal, I think I like \dPn+ very much -- it
> > shows the aggregated size of all partitioned tables. But I think \dP
> > (without the +) is almost completely useless. I'm not really sure about
> > having to add the "n" flag, but I suspect in practical use it's okay.
> >
> > Also, I think \dPn+ shows partitioned tables, but \dPtn+ shows exactly
> > the same, so why do we have the "t" flag at all? We have \dPin+ which
> > shows aggregate size, but the only way to list both tables and indexes
> > is to add a pattern. I think this design was confusingly inspired by
> > the "list" vs. "describe" schizoid dichotomy of \d, without actually
> > getting any useful functionality in return.
> >
> > IMO \dP should be "list partitioned relations with their sizes", and
> > \dPt "list partitioned tables with their sizes", and \dPi "list
> > partitioned indexes with their sizes". If no pattern is given, list
> > them all, otherwise only list those that match the pattern.
>
> After thinking more about this, I'm having second thoughts about the +
> thing. I'm now thinking that requiring the + for computing sizes is
> actually a good thing, because if we change it to show all sizes
> inconditionally, the command becomes unusable for users with lots of
> large partitioned tables. So the submitted patch is okay in that front.
>

I remember, there can be another reason - the size can be different on some
platforms, and display size by default can break regress tests.

>
> Maybe the only behavior change I'd do to the submitted patch is to have
> \dP show both tables and indexes, while \dPt shows only tables and \dPi
> shows only indexes. Maybe have \dPti show both tables and indexes? (
> identical to \dP) That would be consistent with \d itself. Also,
> compare describeFunctions, which allows multiple type specifiers to be
> given.
>

One my idea (maybe not great) was using total relation size, when user
doesn't specify if would to see tables or indexes. Because we support \dPi
(index size) and \dPt (table size), then I though so for \dP there is a
good possibility to use total relation size.

So now \dP is not same as possible \dPti by design. But I can imagine \dPti
as union of \dPt and \dPi

Currently \dP supports both, without pattern then it show tables with
possible total relation size, with pattern it shows tables and indexes with
related sizes - so behave is very similar to \dPti (but it is driven by
pattern usage).

>
> I'm not in love with the way it handles the "n", "t" and "i" specifiers.
> I think we should allow them in any order, not just if the "n" is in
> cmd[2].
>

It is good idea.

>
> --
> Álvaro Herrera https://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2019-04-06 19:27:47 Re: Fix foreign key constraint check for partitioned tables
Previous Message Tom Lane 2019-04-06 19:21:21 Re: tableam scan-API patch broke foreign key validation