correct behavior of ANALYZE ... - Mailing list pgsql-hackers

i came across some interesting behavior of pg_stats and i am not sure if this is something we should treat the way we do it.

consider:

test_hans=# select * from pg_stats where attname = 'id' and tablename = 't_testhugo';

schemaname | tablename  | attname | null_frac | avg_width | n_distinct |  most_common_vals   |                     most_common_freqs                     |       histogram_bounds       | correlation

------------+------------+---------+-----------+-----------+------------+---------------------+-----------------------------------------------------------+------------------------------+-------------

public     | t_testhugo | id      |         0 |         8 |         15 | {18,17,16,19,20,15} | {0.203333,0.186333,0.155333,0.148667,0.0953333,0.0903333} | {11,13,14,14,14,21,21,22,25} |    0.557774

(1 row)

test_hans=# alter TABLE t_testhugo alter column id set statistics 2;

ALTER TABLE

test_hans=# ANALYZE t_testhugo ;

ANALYZE

test_hans=# select * from pg_stats where attname = 'id' and tablename = 't_testhugo';

schemaname | tablename  | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation

------------+------------+---------+-----------+-----------+------------+------------------+-------------------+------------------+-------------

public     | t_testhugo | id      |         0 |         8 |         12 | {18,17}          | {0.21,0.19}       | {12,19,23}       |    0.597255

(1 row)

test_hans=# alter TABLE t_testhugo alter column id set statistics 0;

ALTER TABLE

i expected the histogram to be gone her and stats should be disabled. instead, we keep the old histogram here.

test_hans=# ANALYZE t_testhugo ;

ANALYZE

test_hans=# select * from pg_stats where attname = 'id' and tablename = 't_testhugo';

schemaname | tablename  | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation

------------+------------+---------+-----------+-----------+------------+------------------+-------------------+------------------+-------------

public     | t_testhugo | id      |         0 |         8 |         12 | {18,17}          | {0.21,0.19}       | {12,19,23}       |    0.597255

(1 row)

is that what we expect?

if no, i will go and fit it ...

hans