Date: Mon, 31 Aug 2020 00:18:48 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>,
	Julien Rouhaud <rjuju123@gmail.com>,
	Pavel Stehule <pavel.stehule@gmail.com>,
	pgsql-hackers@lists.postgresql.org,
	Tomas Vondra <tomas.vondra@2ndquadrant.com>
Subject: Re: list of extended statistics on psql
Message-ID: <20200831051848.GH5450@telsasoft.com>
References: <d4fe2cf0-9aa9-8182-25d1-230dd6b98138@nttcom.co.jp_1>
 <20200827235323.GA13919@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20200827235323.GA13919@alvherre.pgsql>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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