AFAICS, the statistics information "the number of dead tuples"
(n_dead_tuples) has an estimation error.

VACUUM sends a message to stats collector process when it has swept a table.
The stats collector receives the message and sets n_dead_tuples of the table
to zero. However, we can update or delete tuples even if a concurrent vacuum
is running through the table. There might be some dead tuples that were
created after start of the vacuum. Therefore, it's not always correct to set
n_dead_tuples to zero at the end of vacuum. Especially, the error will be
worse when a vacuum takes long time.

The autovacuum sees the stats information to decide when to vacuum.
The error in n_dead_tuples misleads it and vacuum starvation might occur
because we always underestimate the number of dead tuples.

I'm thinking to add "the number of vacuumed tuples" to the message from
vacuum. The stats collector will subtract the value from n_dead_tuples
instead of setting it to zero. This is also needed if we want to make
some kinds of "partial" vacuum methods.

Thoughts?  Is this worth doing, I'd like to implement it.

ITAGAKI Takahiro
NTT Open Source Software Center

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not

Reply via email to