From: | Hans-Juergen Schoenig <postgres(at)cybertec(dot)at> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | correct behavior of ANALYZE ... |
Date: | 2007-08-29 16:07:48 |
Message-ID: | 7EEB5886-4691-49E5-B966-A3EF43AAFF91@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
--
Cybertec Geschwinde & Schönig GmbH
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-08-29 16:44:39 | Re: correct behavior of ANALYZE ... |
Previous Message | Tom Lane | 2007-08-29 15:14:49 | Re: StringInfo misc. issues |