Re: list of extended statistics on psql

On 2020/08/31 1:59, Tom Lane wrote:
> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
>> On Sun, Aug 30, 2020 at 12:33:29PM -0400, Alvaro Herrera wrote:
>>> I wonder how to report that. Knowing that psql \-commands are not meant
>>> for anything other than human consumption, maybe we can use a format()
>>> string that says "built: %d bytes" when \dX+ is used (for each stat type),
>>> and just "built" when \dX is used. What do people think about this?
>
> Seems a little too cute to me.
>
>> I'd use the same approach as \d+, i.e. a separate column with the size.
>> Maybe that'd mean too many columns, though.
>
> psql already has \d commands with so many columns that you pretty much
> have to use \x mode to make them legible; \df+ for instance. I don't
> mind if \dX+ is also in that territory. It'd be good though if plain
> \dX can fit in a normal terminal window.

Hmm. How about these instead of "built: %d bytes"?
I added three columns (N_size, D_size, M_size) to show size. See below:

===================
postgres=# \dX
List of extended statistics
Schema | Name | Definition | N_distinct | Dependencies | Mcv
--------+-----------+----------------------------+------------+--------------+-----------
public | stts_1 | a, b FROM t1 | | built |
public | stts_2 | a, b FROM t1 | built | built |
public | stts_3 | a, b FROM t1 | built | built | built
public | stts_4 | b, c FROM t2 | not built | not built | not built
public | stts_hoge | col1, col2, col3 FROM hoge | not built | not built | not built
(5 rows)

postgres=# \dX+
List of extended statistics
Schema | Name | Definition | N_distinct | Dependencies | Mcv | N_size | D_size | M_size
--------+-----------+----------------------------+------------+--------------+-----------+--------+--------+--------
public | stts_1 | a, b FROM t1 | | built | | | 40 |
public | stts_2 | a, b FROM t1 | built | built | | 13 | 40 |
public | stts_3 | a, b FROM t1 | built | built | built | 13 | 40 | 6126
public | stts_4 | b, c FROM t2 | not built | not built | not built | | |
public | stts_hoge | col1, col2, col3 FROM hoge | not built | not built | not built | | |
===================

I used this query to get results of "\dX+".
===================
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 esd.stxdndistinct IS NOT NULL THEN 'built'
WHEN 'd' = any(stxkind) THEN 'not built'
END AS "N_distinct",
CASE WHEN esd.stxddependencies IS NOT NULL THEN 'built'
WHEN 'f' = any(stxkind) THEN 'not built'
END AS "Dependencies",
CASE WHEN esd.stxdmcv IS NOT NULL THEN 'built'
WHEN 'm' = any(stxkind) THEN 'not built'
END AS "Mcv",
pg_catalog.length(stxdndistinct) AS "N_size",
pg_catalog.length(stxddependencies) AS "D_size",
pg_catalog.length(stxdmcv) AS "M_size"
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;
===================

Attached patch includes:

- Replace "Columns" and "Table" column with "Definition"
- Show the status (built/not built/null) of extended stats by
using pg_statistic_ext_data
- Add "\dX+" command to show size of extended stats

Please find the attached file! :-D

Thanks,
Tatsuro Yamada