
Okay, if I'm understanding pg_stats at all, which I may not be, n_distinct
should represent # of distinct values in that row, no?

But, I have one field that has 5 distinct values:

iwantu=# select distinct(profiles_faith) from iwantu_profiles;
 profiles_faith
----------------
              0
              1
              2
              7
              8
(5 rows)

But pg_stats is reporting 1:

    tablename    |        attname         | avg_width | n_distinct
-----------------+------------------------+-----------+------------
 iwantu_profiles | profiles_faith         |         2 |          1

So am I reading n_distinct wrong?


Wed, 13 Feb 2002 09:53:43 -0400 (AST)
"Marc G. Fournier" <scrappy@hub.org>
"Bug" in statistics for v7.2?
"Marc G. Fournier" <scrappy@hub.org> writes:
> Okay, if I'm understanding pg_stats at all, which I may not be, n_distinct
> should represent # of distinct values in that row, no?
> But, I have one field that has 5 distinct values:
> But pg_stats is reporting 1:

The pg_stats values are only, um, statistical.  If 99.9% of the table is
the same value and the other four values appear only once or twice, it's
certainly possible for ANALYZE's sample to include only the common value
and miss the rare ones.  AFAIK that will not break anything; if you have
an example where the planner seems to be fooled because of this, let's
see it.

			regards, tom lane

Wed, 13 Feb 2002 11:11:07 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: "Bug" in statistics for v7.2? 

That explains it ...

 profiles_faith | count
----------------+--------
              0 | 485938
              1 |      2
              2 |      6
              7 |      2
              8 |     21
(5 rows)

Cool, another waste of space *sigh*

thanks ...


On Wed, 13 Feb 2002, Tom Lane wrote:

> "Marc G. Fournier" <scrappy@hub.org> writes:
> > Okay, if I'm understanding pg_stats at all, which I may not be, n_distinct
> > should represent # of distinct values in that row, no?
> > But, I have one field that has 5 distinct values:
> > But pg_stats is reporting 1:
>
> The pg_stats values are only, um, statistical.  If 99.9% of the table is
> the same value and the other four values appear only once or twice, it's
> certainly possible for ANALYZE's sample to include only the common value
> and miss the rare ones.  AFAIK that will not break anything; if you have
> an example where the planner seems to be fooled because of this, let's
> see it.
>
> 			regards, tom lane
>


Wed, 13 Feb 2002 12:15:46 -0400 (AST)
"Marc G. Fournier" <scrappy@hub.org>
Re: "Bug" in statistics for v7.2? 