Re: list of extended statistics on psql - Mailing list pgsql-hackers

Hi Alvaro!

It's been ages since we created a progress reporting feature together. :-D

>>> +1 good idea
>>
>> +1 that's a good idea.  Please add it to the next commitfest!
>
>+1 for the general idea, and +1 for \dX being the syntax to use

Thank you for voting!


> 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?


> Also, the stat obj name column should be first, followed by a single
> column listing both table and columns that it applies to.  Keep in mind
> that in the future we might want to add stats that cross multiple tables
> -- that's why the CREATE syntax is the way it is.  So we should give
> room for that in psql's display too.

I understand your suggestions are the following, right?

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

* 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
===================

*  In the future, Extended stats that cross multiple tables will be
    shown maybe... (t1, t2):
===================
    Name    | Schema | Table  |     Columns      | Ndistinct | Dependencies | MCV
-----------+--------+--------+------------------+-----------+--------------+-----
  stts_5    | public | t1, t2 | a, b             | f         | t            | f
===================

If so, I can revise the column order as you suggested easily.
However, I have no idea how to show extended stats that cross
multiple tables and the status now.

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?


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

         SELECT
         stxnamespace::pg_catalog.regnamespace AS "Schema",
         c.relname AS "Table",
         stxname AS "Name",
         (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)) AS "Columns",
         'd' = any(stxkind) AS "Ndistinct",
         'f' = any(stxkind) AS "Dependencies",
         'm' = any(stxkind) AS "MCV"
         FROM pg_catalog.pg_statistic_ext
         INNER JOIN pg_catalog.pg_class c
         ON stxrelid = c.oid
         ORDER BY 1, 2, 3;

Thanks,
Tatsuro Yamada