Re: list of extended statistics on psql - Mailing list pgsql-hackers
From | Alvaro Herrera |
---|---|
Subject | Re: list of extended statistics on psql |
Date | |
Msg-id | 20200828032617.GA22893@alvherre.pgsql Whole thread Raw |
In response to | Re: list of extended statistics on psql (Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>) |
Responses |
Re: list of extended statistics on psql
(Tomas Vondra <tomas.vondra@2ndquadrant.com>)
Re: list of extended statistics on psql (Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>) |
List | pgsql-hackers |
On 2020-Aug-28, Tatsuro Yamada wrote: > > 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? pg_statistic_ext_data.stxdndistinct is not null if the stats have been built. (I'm not sure whether there's an easier way to determine this.) > * 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 > =================== I suggest to do this Name | Schema | Definition | Ndistinct | Dependencies | MCV -----------+--------+--------------------------+-----------+--------------+----- stts_1 | public | (a, b) FROM t1 | f | t | f > 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? I suggest that changing it in the future is going to be an uphill battle, so better get it right from the get go, without requiring a future restructure. > In addition, > Currently, I use this query to get Extended stats info from pg_statistic_ext. Maybe something like this would do 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 stxdndistinct IS NOT NULL THEN 'built' WHEN 'd' = any(stxkind) THEN 'enabled, not built' END AS "n-distinct", CASE WHEN stxddependencies IS NOT NULL THEN 'built' WHEN 'f' = any(stxkind) THEN 'enabled, not built' END AS "functionaldependencies", CASE WHEN stxdmcv IS NOT NULL THEN 'built' WHEN 'm' = any(stxkind) THEN 'enabled, not built' END AS mcv 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, 3; -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
pgsql-hackers by date: