Thread: pg_stats in 9.2

Hi,

I try to understand the new columns in pg_stats in 9.2, but I'm kinda
puzzled by the results.

Here is the test case I'm working on:

CREATE TABLE t1(c1 integer, c2 integer[]);
INSERT INTO t1 select 1, '{4}';
INSERT INTO t1 select 2, '{5}';
INSERT INTO t1 select 3, '{6}';
ANALYZE t1;

SELECT * FROM pg_stats WHERE tablename='t1';
-[ RECORD 1 ]----------+-------------------------------
schemaname             | public
tablename              | t1
attname                | c1
inherited              | f
null_frac              | 0
avg_width              | 4
n_distinct             | -1
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {1,2,3}
correlation            | 1
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |

This record seems good to me. It's a scalar value, and
most_common_elems, most_common_elem_freqs, and elem_count_histogram are
NULL. Fine with me.

Now the second record:

-[ RECORD 2 ]----------+-------------------------------
schemaname             | public
tablename              | t1
attname                | ahah
inherited              | f
null_frac              | 0
avg_width              | 25
n_distinct             | -1
most_common_vals       |
most_common_freqs      |
histogram_bounds       | {"{4}","{5}","{6}"}
correlation            | 1
most_common_elems      | {4,5,6}
most_common_elem_freqs |
{0.333333,0.333333,0.333333,0.333333,0.333333,0}
elem_count_histogram   |

{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1}

most_common_elems seems right (all the distinct values in my arrays).
But I fail to understand why I have so many items in
most_common_elem_freqs array. I was expecting only 3, but got 6. Why?
And if I make the sum, I'm way above 1. Not sure why.

Finally, he elem_count_histogram column value doesn't make any sense to
me.

Anyone care to explain all this to me? :)

Thanks.

Regards.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Guillaume Lelarge <guillaume@lelarge.info> writes:
> Anyone care to explain all this to me? :)

Try the stats-slot type specifications in
src/include/catalog/pg_statistic.h

            regards, tom lane

On Wed, 2012-10-17 at 11:57 -0400, Tom Lane wrote:
> Guillaume Lelarge <guillaume@lelarge.info> writes:
> > Anyone care to explain all this to me? :)
>
> Try the stats-slot type specifications in
> src/include/catalog/pg_statistic.h
>

Oh, OK, got it.

The three more values are, in order, the smaller frequency, the bigger
frequency, and the frequency of NULL elements.

Thanks.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com