From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>
Cc: Julien Rouhaud <rjuju123@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Subject: Re: list of extended statistics on psql
Date: Thu, 27 Aug 2020 23:26:17 -0400
Message-ID: <20200828032617.GA22893@alvherre.pgsql> (raw)
In-Reply-To: <6fc07377-c8c5-c762-2e2c-77059d8147af@nttcom.co.jp_1>
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
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: alvherre@2ndquadrant.com, tatsuro.yamada.tf@nttcom.co.jp, rjuju123@gmail.com, pavel.stehule@gmail.com, pgsql-hackers@lists.postgresql.org, tomas.vondra@2ndquadrant.com
Subject: Re: list of extended statistics on psql
In-Reply-To: <20200828032617.GA22893@alvherre.pgsql>
* 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