Re: autoanalyze criteria

On Feb 25, 2013, at 7:23, Stefan Andreatta <s(dot)andreatta(at)synedra(dot)com> wrote:

> On 02/24/2013 12:52 PM, Alban Hertroys wrote:
>> On Feb 23, 2013, at 14:11, Stefan Andreatta <s(dot)andreatta(at)synedra(dot)com> wrote:
>>
>>> And we are still missing a number for rows updated since the last analyse.
>>
>> In MVCC an update is an insert + delete, so you already got those numbers.
>>
> 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.