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

From Tatsuro Yamada
Subject Re: [spam] Re: list of extended statistics on psql
Date
Msg-id d4663da7-29e0-2a44-54a8-27b8da1584f6@nttcom.co.jp_1
Whole thread Raw
In response to Re: list of extended statistics on psql  (Michael Paquier <michael@paquier.xyz>)
List pgsql-hackers
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

Attachment

pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: duplicate function oid symbols
Next
From: Tatsuo Ishii
Date:
Subject: Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits