Re: list of extended statistics on psql

On 2020/08/31 1:59, Tom Lane wrote:
> Tomas Vondra <tomas.vondra@2ndquadrant.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