select statement against pg_stats returns inconsistent data - Mailing list pgsql-general

The select statements return different data for
most_commons_vals depending on whether n_distinct is
included in the select clause or not.

I only seem to get the behavior below against int8
columns - but I haven't interated through every
conceivable data type either.

Is this expected behavior or perhaps a bug?

Regards,

Shelby Cain

=========================================================

c1scain=# select version();
                                        version

---------------------------------------------------------------------------------------
 PostgreSQL 7.4.1 on i686-pc-cygwin, compiled by GCC
gcc (GCC) 3.3.1 (cygming special)
(1 row)

c1scain=# create table test_table (lastname
varchar(20), firstname varchar(20), userid int8,
testid int8);
CREATE TABLE
c1scain=# insert into test_table values ('cain',
'shelby', random()*10000, random()*10000);
INSERT 13015123 1
c1scain=# insert into test_table values ('cain',
'shelby', random()*10000, random()*10000);
INSERT 13015124 1
c1scain=# insert into test_table values ('cain',
'shelby', random()*10000, random()*10000);
INSERT 13015125 1
c1scain=# insert into test_table values ('cain',
'shelby', random()*10000, random()*10000);
INSERT 13015126 1
c1scain=# insert into test_table values ('cain',
'shelby', random()*10000, random()*10000);
INSERT 13015127 1
c1scain=# insert into test_table (select * from
test_table);
INSERT 0 5
c1scain=# insert into test_table (select * from
test_table);
INSERT 0 10
c1scain=# insert into test_table (select * from
test_table);
INSERT 0 20
c1scain=# insert into test_table (select * from
test_table);
INSERT 0 40
c1scain=# analyze test_table;
ANALYZE
c1scain=# select distinct userid from test_table;
 userid
--------
    211
   2641
   4333
   7642
   8053
(5 rows)
c1scain=# select distinct testid from test_table;
 testid
--------
     73
    834
   1399
   2315
   4511
(5 rows)
c1scain=# select tablename, attname, most_common_vals
from pg_stats where tablename = 'test_table';
 tablename  |  attname  |     most_common_vals
------------+-----------+---------------------------
 test_table | lastname  | {cain}
 test_table | firstname | {shelby}
 test_table | userid    | {211,2641,4333,7642,8053}
 test_table | testid    | {73,834,1399,2315,4511}
(4 rows)
c1scain=# select tablename, attname, n_distinct,
most_common_vals from pg_stats where tablename =
'test_table';
 tablename  |  attname  | n_distinct |
  most_common_vals
------------+-----------+------------+------------------------------------------------------
 test_table | lastname  |          1 | {cain}
 test_table | firstname |          1 | {shelby}
 test_table | userid    |          5 |
{211,18610093293568,32822140076032,34587371634688,0}
 test_table | testid    |          5 |
{73,6008659247104,9942849290240,19374597472256,0}


__________________________________
Do you Yahoo!?
Yahoo! Mail SpamGuard - Read only the mail you want.
http://antispam.yahoo.com/tools