Обсуждение: [GENERAL] unnest-like pg_stats.most_common_values and pg_stats.most_common_freqs

Hi all,
maybe this is trivial, but I need an hint on a way to see a table form
of the MCVs and MCFs out of pg_stats with a query. Is it possible to
get a set of rows each with a most common value on one column and the
corresponding column on the the other? (assuming I can cast the array
of MCVs to the right type array)

Thanks,
Luca


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Nov 17, 2017 at 09:32:23PM +0100, Luca Ferrari wrote:
> Hi all,
> maybe this is trivial, but I need an hint on a way to see a table form
> of the MCVs and MCFs out of pg_stats with a query. Is it possible to
> get a set of rows each with a most common value on one column and the
> corresponding column on the the other? (assuming I can cast the array
> of MCVs to the right type array)

I think you want something like this ?

postgres=# SELECT schemaname, tablename, attname, unnest(histogram_bounds::text::text[]), histogram_bounds FROM
pg_statsLIMIT 9;pg_catalog | pg_pltemplate | tmplname    | plperl              |
{plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog| pg_pltemplate | tmplname    | plperlu
          | {plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog | pg_pltemplate | tmplname
 | plpgsql             | {plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog |
pg_pltemplate| tmplname    | plpython2u          |
{plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog| pg_pltemplate | tmplname    |
plpython3u         | {plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog | pg_pltemplate |
tmplname   | plpythonu           | {plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog |
pg_pltemplate| tmplname    | pltcl               |
{plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog| pg_pltemplate | tmplname    | pltclu
          | {plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}pg_catalog | pg_pltemplate |
tmplhandler| plperl_call_handler |
{plperl_call_handler,plperlu_call_handler,plpgsql_call_handler,plpython2_call_handler,plpython3_call_handler,plpython_call_handler,pltcl_cal
l_handler,pltclu_call_handler}

Justin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Nov 17, 2017 at 9:41 PM, Justin Pryzby <pryzby@telsasoft.com> wrote:
> I think you want something like this ?
>
> postgres=# SELECT schemaname, tablename, attname, unnest(histogram_bounds::text::text[]), histogram_bounds FROM
pg_statsLIMIT 9;
 
>  pg_catalog | pg_pltemplate | tmplname    | plperl              |
{plperl,plperlu,plpgsql,plpython2u,plpython3u,plpythonu,pltcl,pltclu}

I don't think it is what I'm looking for, I would like something ,like:
select unnest( histogram_bounds::text::text[] ), unnest(
most_common_freqs ) from pg_stats

but with correlation between the two array indexes. Is it something
achievable in SQL? Or should I use a plpgsql loop with an index?

Luca


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Luca Ferrari <fluca1978@infinito.it> writes:
> I don't think it is what I'm looking for, I would like something ,like:
>  select unnest( histogram_bounds::text::text[] ), unnest(
> most_common_freqs ) from pg_stats
> but with correlation between the two array indexes. Is it something
> achievable in SQL? Or should I use a plpgsql loop with an index?

Those two aren't correlated ... but I think what you want is

select ...,v,f
from pg_stats, rows from (unnest(most_common_vals::text::text[]),            unnest(most_common_freqs)) r(v,f)
where ...

Seems to work back to 9.4.
        regards, tom lane

On Mon, Nov 20, 2017 at 4:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Those two aren't correlated ... but I think what you want is
>
> select ...,v,f
> from
>   pg_stats,
>   rows from (unnest(most_common_vals::text::text[]),
>              unnest(most_common_freqs)) r(v,f)
> where ...

Of course I was meaning MCVs and MCFs, it did not make sense to use
the histogram here!
Thanks for the query, it works (tested on 9.6.6).

Luca