Re: list of extended statistics on psql - Mailing list pgsql-hackers
From | Tatsuro Yamada |
---|---|
Subject | Re: list of extended statistics on psql |
Date | |
Msg-id | 2a3173fb-8571-3779-2916-d16a170750f0@nttcom.co.jp_1 Whole thread Raw |
In response to | Re: list of extended statistics on psql (Michael Paquier <michael@paquier.xyz>) |
Responses |
Re: list of extended statistics on psql
(Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>)
Re: list of extended statistics on psql (Tomas Vondra <tomas.vondra@2ndquadrant.com>) |
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: