unnest-like pg_stats.most_common_values and pg_stats.most_common_freqs

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_stats LIMIT 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_stats LIMIT 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