Re: autoanalyze criteria

On Feb 25, 2013, at 7:23, Stefan Andreatta <s.andreatta@synedra.com> =
wrote:

> On 02/24/2013 12:52 PM, Alban Hertroys wrote:
>> On Feb 23, 2013, at 14:11, Stefan Andreatta <s.andreatta@synedra.com> =
wrote:
>>=20
>>> And we are still missing a number for rows updated since the last =
analyse.
>>=20
>> In MVCC an update is an insert + delete, so you already got those =
numbers.
>>=20
> Good point. But because they are an update and a delete, they cancel =
each other out and do not show up in pg_stat_user_tables.n_live_tup - =
and that's the only value for which we have a reference value from the =
time of the last analyze (pg_class.reltuples).

I'm pretty sure that an update results in 1 live + 1 dead tuple, so they =
don't cancel each other out - they end up adding to different =
statistics. Assuming those statistics are both since last vacuum, added =
together they are the total number of changed records since last vacuum.
What gain do you expect from a number of updated tuples?

And it seems to me those numbers are since last vacuum, not since last =
analyse - analyse doesn't change the amount of dead tuples (it just =
updates them to closer match reality), but vacuum does.

Disclaimer: I'm not intimately familiar with the planner statistics, but =
knowing what vacuum and analyse do in an MVCC database, like I described =
above it makes sense to me. I might be wrong though.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.