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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
