From: | Tatsuro Yamada <tatsuro(dot)yamada(dot)tf(at)nttcom(dot)co(dot)jp> |
---|---|
To: | Michael Paquier <michael(at)paquier(dot)xyz> |
Cc: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Julien Rouhaud <rjuju123(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: list of extended statistics on psql |
Date: | 2020-10-28 06:07:56 |
Message-ID: | 2a3173fb-8571-3779-2916-d16a170750f0@nttcom.co.jp_1 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 | Content-Type | Size |
---|---|---|
add_list_extended_stats_for_psql_by_dX_and_dXplus_r4.patch | text/plain | 11.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | torikoshia | 2020-10-28 06:32:15 | Re: Get memory contexts of an arbitrary backend process |
Previous Message | Tatsuo Ishii | 2020-10-28 05:46:53 | Re: COPY FREEZE and setting PD_ALL_VISIBLE/visibility map bits |