From: | Tatsuro Yamada <tatsuro(dot)yamada(dot)tf(at)nttcom(dot)co(dot)jp> |
---|---|
To: | Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, "Shinoda, Noriyoshi (PN Japan FSIP)" <noriyoshi(dot)shinoda(at)hpe(dot)com>, Julien Rouhaud <rjuju123(at)gmail(dot)com> |
Cc: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Michael Paquier <michael(at)paquier(dot)xyz>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: list of extended statistics on psql |
Date: | 2021-01-19 03:02:02 |
Message-ID: | ad31aaac-85eb-dfed-28e6-afc0f23c7381@nttcom.co.jp_1 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
> The above query is so simple so that we would better to use the following query:
>
> # This query works on PG10 or later
> SELECT
> es.stxnamespace::pg_catalog.regnamespace::text AS "Schema",
> es.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 'd' = any(es.stxkind) THEN 'defined'
> END AS "Ndistinct",
> CASE WHEN 'f' = any(es.stxkind) THEN 'defined'
> END AS "Dependencies",
> CASE WHEN 'm' = any(es.stxkind) THEN 'defined'
> END AS "MCV"
> FROM pg_catalog.pg_statistic_ext es
> ORDER BY 1, 2;
>
> Schema | Name | Definition | Ndistinct | Dependencies | Dependencies
> --------+------------+------------------+-----------+--------------+--------------
> public | hoge1_ext | a, b FROM hoge1 | defined | defined | defined
> public | hoge_t_ext | a, b FROM hoge_t | defined | defined | defined
> (2 rows)
>
>
> I'm going to create the WIP patch to use the above query.
> Any comments welcome. :-D
Attached patch is WIP patch.
The changes are:
- Use pg_statistic_ext only
- Remove these statuses: "required" and "built"
- Add new status: "defined"
- Remove the size columns
- Fix document
I'll create and send the regression test on the next patch if there is
no objection. Is it Okay?
Regards,
Tatsuro Yamada
Attachment | Content-Type | Size |
---|---|---|
WIP_psql_dX_using_pg_statistic_ext.patch | text/plain | 6.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Dilip Kumar | 2021-01-19 03:04:24 | Re: Is Recovery actually paused? |
Previous Message | Zhihong Yu | 2021-01-19 03:01:47 | Re: simplifying foreign key/RI checks |