Re: Autovacuum versus rolled-back transactions

[ITAGAKI Takahiro <itagaki(dot)takahiro(at)oss(dot)ntt(dot)co(dot)jp>]

Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:

> Tom Lane wrote:
> 
> > It may boil down to whether we would like the identity
> > 	n_live_tup = n_tup_ins - n_tup_del
> > to continue to hold, or the similar one for n_dead_tup.  The problem
> > basically is that pgstats is computing n_live_tup and n_dead_tup
> > using those identities rather than by tracking what really happens.

On a relevant note, there is a variance in the calculation of auto-analyze
threshold between documentation and implementation in HEAD.
(Only HEAD; It is ok in 8.2 or before)

Our documentation says
| analyze threshold = analyze base threshold
|                       + analyze scale factor * number of tuples
| is compared to the total number of tuples inserted, updated, or deleted
| since the last ANALYZE. 
http://momjian.us/main/writings/pgsql/sgml/routine-vacuuming.html#AUTOVACUUM

but deleted tuples are not considered in the total number, because the delta
of {n_live_tuples + n_dead_tuples} is not changed by DELETE. We add the number
of DELETE into n_live_tuples and subtract it from n_dead_tuples.

| pgstat.c
|   t_new_live_tuples += tuples_inserted - tuples_deleted;
|   t_new_dead_tuples += tuples_deleted;
| autovacuum.c
|   anltuples = n_live_tuples + n_dead_tuples - last_anl_tuples;

There is no delete-only database in the real world, so this is not so serious
problem probably. We'd better to fix the documentation if it is intention.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center