From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>
Cc: Julien Rouhaud <rjuju123@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org, Tomas Vondra <tomas.vondra@2ndquadrant.com>
Subject: Re: list of extended statistics on psql
Date: Mon, 31 Aug 2020 00:18:48 -0500
Message-ID: <20200831051848.GH5450@telsasoft.com> (raw)
In-Reply-To: <20200827235323.GA13919@alvherre.pgsql>
References: <d4fe2cf0-9aa9-8182-25d1-230dd6b98138@nttcom.co.jp_1>
<20200827235323.GA13919@alvherre.pgsql>
On Thu, Aug 27, 2020 at 07:53:23PM -0400, Alvaro Herrera wrote:
> +1 for the general idea, and +1 for \dX being the syntax to use
>
> IMO the per-type columns should show both the type being enabled as
> well as it being built.
>
> (How many more stat types do we expect -- Tomas? I wonder if having one
> column per type is going to scale in the long run.)
>
> Also, the stat obj name column should be first, followed by a single
> column listing both table and columns that it applies to. Keep in mind
> that in the future we might want to add stats that cross multiple tables
> -- that's why the CREATE syntax is the way it is. So we should give
> room for that in psql's display too.
There's also a plan for CREATE STATISTICS to support expresion statistics, with
the statistics functionality of an expression index, but without the cost of
index-update on UPDATE/DELETE. That's Tomas' patch here:
https://commitfest.postgresql.org/29/2421/
I think that would compute ndistinct and MCV, same as indexes, but not
dependencies. To me, I think it's better if there's a single column showing
the "kinds" of statistics to be generated (stxkind), rather than a column for
each.
I'm not sure why the length of the stats lists cast as text is useful to show?
We don't have a slash-dee command to show the number of MCV or histogram in
traditional, 1-D stats in pg_statistic, right ? I think anybody wanting that
would learn to SELECT FROM pg_statistic*. Also, the length of the text output
isn't very meaningful ? If this is json, maybe you'd do something like this:
|SELECT a.stxdndistinct , COUNT(b) FROM pg_statistic_ext_data a , json_each(stxdndistinct::Json) AS b GROUP BY 1
I guess stxdmcv isn't json, but it seems especially meaningless to show
length() of its ::text, since we don't even "deserialize" the object to begin
with.
BTW, I've just started a new thread about displaying in psql \d the stats
target of target extended stats.
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, alvherre@2ndquadrant.com, tatsuro.yamada.tf@nttcom.co.jp, rjuju123@gmail.com, pavel.stehule@gmail.com, tomas.vondra@2ndquadrant.com
Subject: Re: list of extended statistics on psql
In-Reply-To: <20200831051848.GH5450@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox