Re: list of extended statistics on psql

Hi Michael-san and Hackers,

On 2020/09/30 15:19, Michael Paquier wrote:
> On Thu, Sep 17, 2020 at 02:55:31PM +0900, Michael Paquier wrote:
>> Could you provide at least a rebased version of the patch? The CF bot
>> is complaning here.
>
> Not seeing this answered after two weeks, I have marked the patch as
> RwF for now.
> --
> Michael

Sorry for the delayed reply.

I re-based the patch on the current head and did some
refactoring.
I think the size of extended stats are not useful for DBA.
Should I remove it?

Changes:
========
- Use a keyword "defined" instead of "not built"
- Use COALESCE function for size for extended stats

Results of \dX and \dX+:
========================
postgres=# \dX
List of extended statistics
Schema | Name | Definition | N_distinct | Dependencies | Mcv
-------------+-----------+-----------------+------------+--------------+---------
public | hoge1_ext | a, b FROM hoge1 | defined | defined | defined
hoge1schema | hoge1_ext | a, b FROM hoge1 | built | built | built
(2 rows)

postgres=# \dX+
List of extended statistics
Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
-------------+-----------+-----------------+------------+--------------+---------+--------+--------+--------
public | hoge1_ext | a, b FROM hoge1 | defined | defined | defined | 0 | 0 | 0
hoge1schema | hoge1_ext | a, b FROM hoge1 | built | built | built | 13 | 40 | 6126
(2 rows)

Query of \dX+:
==============
SELECT
stxnamespace::pg_catalog.regnamespace AS "Schema",
stxname AS "Name",
pg_catalog.format('%s FROM %s',
(SELECT pg_catalog.string_agg(pg_catalog.quote_ident(a.attname),', ')
FROM pg_catalog.unnest(es.stxkeys) s(attnum)
JOIN pg_catalog.pg_attribute a
ON (es.stxrelid = a.attrelid
AND a.attnum = s.attnum
AND NOT a.attisdropped)),
es.stxrelid::regclass) AS "Definition",
CASE WHEN esd.stxdndistinct IS NOT NULL THEN 'built'
WHEN 'd' = any(stxkind) THEN 'defined'
END AS "N_distinct",
CASE WHEN esd.stxddependencies IS NOT NULL THEN 'built'
WHEN 'f' = any(stxkind) THEN 'defined'
END AS "Dependencies",
CASE WHEN esd.stxdmcv IS NOT NULL THEN 'built'
WHEN 'm' = any(stxkind) THEN 'defined'
END AS "Mcv",
COALESCE(pg_catalog.length(stxdndistinct), 0) AS "N_size",
COALESCE(pg_catalog.length(stxddependencies), 0) AS "D_size",
COALESCE(pg_catalog.length(stxdmcv), 0) AS "M_size"
FROM pg_catalog.pg_statistic_ext es
LEFT JOIN pg_catalog.pg_statistic_ext_data esd
ON es.oid = esd.stxoid
INNER JOIN pg_catalog.pg_class c
ON es.stxrelid = c.oid
ORDER BY 1, 2;

Regards,
Tatsuro Yamada