Re: [HACKERS] correct behavior of ANALYZE ...
hi tom ... the idea behind this is to disable the stats on a certain column entirely. this would give me more control about the plan. in this special case data is changing so frequently that the default values are ways better than trying to keep the "real" stats up to date. in case of default value i know what the DB does given a certain where clause - this is beyond my control when stats drop in. i guess there are corner cases where no stats on certain fields can definitely help to make plans a little bit more stable. many thanks, hans On Aug 29, 2007, at 6:44 PM, Tom Lane wrote: Hans-Juergen Schoenig <[EMAIL PROTECTED]> writes: 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. Setting target zero means "expend no work on this column". In my book that includes not doing anything to any pre-existing pg_stats entry. What you propose would defeat the ability to analyze an unchanging column once and then make ANALYZE skip over it henceforth. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- 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
Re: [HACKERS] correct behavior of ANALYZE ...
Hans-Juergen Schoenig <[EMAIL PROTECTED]> writes: > 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. Setting target zero means "expend no work on this column". In my book that includes not doing anything to any pre-existing pg_stats entry. What you propose would defeat the ability to analyze an unchanging column once and then make ANALYZE skip over it henceforth. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] correct behavior of ANALYZE ...
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.20,0.186333,0.155333,0.148667,0.095,0.090} |
{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
