Re: [SQL] 7.3 analyze & vacuum analyze problem

On Wed, 30 Apr 2003, Tom Lane wrote:

> "Ron Mayer" <ron(at)intervideo(dot)com> writes:
> > Short summary: Later in the thread Tom explained my problem as free
> > space not being evenly distributed across the table so ANALYZE's
> > sampling gave skewed results. In my case, "pgstatuple" was a
> > good tool for diagnosing the problem, "vacuum full" fixed my table
> > and a much larger fsm_* would have probably prevented it.
>
> Not sure if that is Achilleus' problem or not. IIRC, there should be
> no difference at all in what VACUUM ANALYZE and ANALYZE put into
> pg_statistic (modulo random sampling variations of course). The only
> difference is that VACUUM ANALYZE puts an exact tuple count into
> pg_class.reltuples (since the VACUUM part groveled over every tuple,
> this info is available) whereas ANALYZE does not scan the entire table
> and so has to put an estimate into pg_class.reltuples.
>
> It would be interesting to see the pg_class and pg_stats rows for this
> table after VACUUM ANALYZE and after ANALYZE --- but I suspect the main
> difference will be the reltuples values.

Unfortunately i did a VACUUM FULL, and later a dump/reload
which eliminated (vanished) the problem regarding the difference between
plain ANALYZE and VACUUM ANALYZE.

However, now the condition is much more wierd, in the sense
that after the reload, some planner costs seem too low (~ 6)
the expected number of rows is very often 1,
and the correct index is used, resulting in a
ultra speed situation (that i never had expected!).

After vacuum full analyze, or vacuum analyze
things get slow again.

I surely must generate a reproducable scenario,
describing the exact steps made, so i'll focus
on that.

In the meantime if Tom or some other hacker
has any ideas that would be great.

>
> regards, tom lane
>

--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill(at)matrix(dot)gatewaynet(dot)com
mantzios(at)softlab(dot)ece(dot)ntua(dot)gr