From: Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: Shinoda, Noriyoshi (PN Japan FSIP) <noriyoshi.shinoda@hpe.com>
To: Julien Rouhaud <rjuju123@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Michael Paquier <michael@paquier.xyz>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: list of extended statistics on psql
Date: Mon, 18 Jan 2021 16:31:56 +0900
Message-ID: <e5268d6a-c361-e017-4eb7-175a2f3fd8fc@nttcom.co.jp_1> (raw)
In-Reply-To: <a9c2eb3f-a4e7-b196-9a87-f2acfb5c4859@enterprisedb.com>
References: <20210107144713.GA8872@alvherre.pgsql>
<61cba5af-5a58-6bda-610c-420077b780c3@enterprisedb.com>
<1045d2cb-7ecd-e6bf-cb50-8eeea89cc4b0@nttcom.co.jp_1>
<9e76ce7a-e7ab-5f08-9938-59fffc20d106@enterprisedb.com>
<29e16ceb-8777-4297-19c8-245242dd7473@enterprisedb.com>
<876efa7e-2067-a4f9-d7e8-f6025440c870@nttcom.co.jp_1>
<0e84aa72-25f2-1849-753f-f827949132b3@enterprisedb.com>
<85e7e1e7-c2f0-a4a0-61de-6e7ecaee44e3@nttcom.co.jp_1>
<79d3a4c5-1efe-5b9e-c06e-3a907b531013@nttcom.co.jp_1>
<20210115084741.GA91293@nol>
<2eb51458-7475-17d3-cb13-b3c950b2f930@enterprisedb.com>
<5e0fb573-3c38-ee39-885c-bdc01f116335@enterprisedb.com>
<TU4PR8401MB115239DC3FE567BE637F7544EEA50@TU4PR8401MB1152.NAMPRD84.PROD.OUTLOOK.COM>
<d876a1e3-9a6c-712e-7ab8-3dfa8a4cac1e@enterprisedb.com>
<a9c2eb3f-a4e7-b196-9a87-f2acfb5c4859@enterprisedb.com>
Hi Tomas and Shinoda-san,
On 2021/01/17 23:31, Tomas Vondra wrote:
>
>
> On 1/17/21 3:01 AM, Tomas Vondra wrote:
>> On 1/17/21 2:41 AM, Shinoda, Noriyoshi (PN Japan FSIP) wrote:
>>> Hi, hackers.
>>>
>>> I tested this committed feature.
>>> It doesn't seem to be available to non-superusers due to the inability to access pg_statistics_ext_data.
>>> Is this the expected behavior?
Ugh. I overlooked the test to check the case of the user hasn't Superuser privilege. The user without the privilege was able to access pg_statistics_ext. Therefore I supposed that it's also able to access pg_statics_ext_data. Oops.
>> Hmmm, that's a good point. Bummer we haven't noticed that earlier.
>>
>> I wonder what the right fix should be - presumably we could do something like pg_stats_ext (we can't use that view directly, because it formats the data, so the sizes are different).
>>
>> But should it list just the stats the user has access to, or should it list everything and leave the inaccessible fields NULL?
>>
>
> I've reverted the commit - once we find the right way to handle this, I'll get it committed again.
>
> As for how to deal with this, I can think of about three ways:
>
> 1) simplify the command to only print information from pg_statistic_ext (so on information about which stats are built or sizes)
>
> 2) extend pg_stats_ext with necessary information (e.g. sizes)
>
> 3) create a new system view, with necessary information (so that pg_stats_ext does not need to be modified)
>
> 4) add functions returning the necessary information, possibly only for statistics the user can access (similarly to what pg_stats_ext does)
>
> Options 2-4 have the obvious disadvantage that this won't work on older releases (we can't add views or functions there). So I'm leaning towards #1 even if that means we have to remove some of the details. We can consider adding that for new releases, though.
Thanks for the useful advice. I go with option 1).
The following query is created by using pg_stats_ext instead of pg_statistic_ext and pg_statistic_ext_data. However, I was confused
about writing a part of the query for calculating MCV size because
there are four columns related to MCV. For example, most_common_vals, most_common_val_nulls, most_common_freqs, and most_common_base_freqs.
Currently, I don't know how to calculate the size of MCV by using the
four columns. Thoughts? :-)
===================================================
\connect postgres hoge
create table hoge_t(a int, b int);
insert into hoge_t select i,i from generate_series(1,100) i;
create statistics hoge_t_ext on a, b from hoge_t;
SELECT
es.statistics_schemaname AS "Schema",
es.statistics_name AS "Name",
pg_catalog.format('%s FROM %s',
(SELECT pg_catalog.string_agg(pg_catalog.quote_ident(s.attname),', ')
FROM pg_catalog.unnest(es.attnames) s(attname)),
es.tablename) AS "Definition",
CASE WHEN es.n_distinct IS NOT NULL THEN 'built'
WHEN 'd' = any(es.kinds) THEN 'requested'
END AS "Ndistinct",
CASE WHEN es.dependencies IS NOT NULL THEN 'built'
WHEN 'f' = any(es.kinds) THEN 'requested'
END AS "Dependencies",
CASE WHEN es.most_common_vals IS NOT NULL THEN 'built'
WHEN 'm' = any(es.kinds) THEN 'requested'
END AS "MCV",
CASE WHEN es.n_distinct IS NOT NULL THEN
pg_catalog.pg_size_pretty(pg_catalog.length(es.n_distinct)::bigint)
WHEN 'd' = any(es.kinds) THEN '0 bytes'
END AS "Ndistinct_size",
CASE WHEN es.dependencies IS NOT NULL THEN
pg_catalog.pg_size_pretty(pg_catalog.length(es.dependencies)::bigint)
WHEN 'f' = any(es.kinds) THEN '0 bytes'
END AS "Dependencies_size"
FROM pg_catalog.pg_stats_ext es
ORDER BY 1, 2;
-[ RECORD 1 ]-----+-----------------
Schema | public
Name | hoge_t_ext
Definition | a, b FROM hoge_t
Ndistinct | requested
Dependencies | requested
MCV | requested
Ndistinct_size | 0 bytes
Dependencies_size | 0 bytes
analyze hoge_t;
-[ RECORD 1 ]-----+-----------------
Schema | public
Name | hoge_t_ext
Definition | a, b FROM hoge_t
Ndistinct | built
Dependencies | built
MCV | built
Ndistinct_size | 13 bytes
Dependencies_size | 40 bytes
===================================================
Thanks,
Tatsuro Yamada
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: tatsuro.yamada.tf@nttcom.co.jp, tomas.vondra@enterprisedb.com, noriyoshi.shinoda@hpe.com, rjuju123@gmail.com, alvherre@2ndquadrant.com, tomas.vondra@2ndquadrant.com, michael@paquier.xyz, pavel.stehule@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: list of extended statistics on psql
In-Reply-To: <e5268d6a-c361-e017-4eb7-175a2f3fd8fc@nttcom.co.jp_1>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox