Lists: | pgsql-general |
---|
From: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | pg_stats in 9.2 |
Date: | 2012-10-17 15:48:31 |
Message-ID: | 1350488911.2127.35.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: pg_stats in 9.2 |
Date: | 2012-10-17 15:57:58 |
Message-ID: | 15049.1350489478@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Guillaume Lelarge <guillaume(at)lelarge(dot)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
From: | Guillaume Lelarge <guillaume(at)lelarge(dot)info> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: pg_stats in 9.2 |
Date: | 2012-10-18 20:09:09 |
Message-ID: | 1350590949.7906.41.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Wed, 2012-10-17 at 11:57 -0400, Tom Lane wrote:
> Guillaume Lelarge <guillaume(at)lelarge(dot)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