Date: Thu, 27 Aug 2020 23:26:17 -0400
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>
Cc: Julien Rouhaud <rjuju123@gmail.com>,
	Pavel Stehule <pavel.stehule@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Tomas Vondra <tomas.vondra@2ndquadrant.com>
Subject: Re: list of extended statistics on psql
Message-ID: <20200828032617.GA22893@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <6fc07377-c8c5-c762-2e2c-77059d8147af@nttcom.co.jp_1>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk

On 2020-Aug-28, Tatsuro Yamada wrote:

> > IMO the per-type columns should show both the type being enabled as
> > well as it being built.
> 
> Hmm. I'm not sure how to get the status (enabled or disabled) of
> extended stats. :(
> Could you explain it more?

pg_statistic_ext_data.stxdndistinct is not null if the stats have been
built.  (I'm not sure whether there's an easier way to determine this.)


> * The suggested column order is like this:
> ===================
>    Name    | Schema | Table |     Columns      | Ndistinct | Dependencies | MCV
> -----------+--------+-------+------------------+-----------+--------------+-----
>  stts_1    | public | t1    | a, b             | f         | t            | f
>  stts_2    | public | t1    | a, b             | t         | t            | f
>  stts_3    | public | t1    | a, b             | t         | t            | t
>  stts_4    | public | t2    | b, c             | t         | t            | t
> ===================

I suggest to do this

    Name    | Schema | Definition               | Ndistinct | Dependencies | MCV
 -----------+--------+--------------------------+-----------+--------------+-----
  stts_1    | public | (a, b) FROM t1           | f         | t            | f

> I suppose that the current column order is sufficient if there is
> no improvement of extended stats on PG14. Do you know any plan to
> improve extended stats such as to allow it to cross multiple tables on PG14?

I suggest that changing it in the future is going to be an uphill
battle, so better get it right from the get go, without requiring a
future restructure.

> In addition,
> Currently, I use this query to get Extended stats info from pg_statistic_ext.

Maybe something like this would do

SELECT
 stxnamespace::pg_catalog.regnamespace AS "Schema",
 stxname AS "Name",
 format('%s FROM %s',
 (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')
  FROM pg_catalog.unnest(stxkeys) s(attnum)
  JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND
  a.attnum = s.attnum AND NOT attisdropped)),
  stxrelid::regclass) AS "Definition",
  CASE WHEN stxdndistinct IS NOT NULL THEN 'built' WHEN 'd' = any(stxkind) THEN 'enabled, not built' END AS "n-distinct",
  CASE WHEN stxddependencies IS NOT NULL THEN 'built' WHEN 'f' = any(stxkind) THEN 'enabled, not built' END AS "functional dependencies",
  CASE WHEN stxdmcv IS NOT NULL THEN 'built' WHEN 'm' = any(stxkind) THEN 'enabled, not built' END AS mcv
 FROM pg_catalog.pg_statistic_ext es
 INNER JOIN pg_catalog.pg_class c
 ON stxrelid = c.oid
 LEFT JOIN pg_catalog.pg_statistic_ext_data esd ON es.oid = esd.stxoid
 ORDER BY 1, 2, 3;

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