Re: Partial vacuum versus pg_class.reltuples

On Sat, 2009-06-06 at 15:44 -0400, Tom Lane wrote:

> I think probably the only workable solution for 8.4 is to use the prior
> value of the relation's reltuples field as num_heap_tuples when we have
> not scanned the whole heap. This will effectively mean that index
> reltuples values don't change either in a partial vacuum.

Our analysis in 2006 was that HOT had a very wide use case, while
partial vacuum was needed, but in far fewer cases. ISTM that most
vacuums will still be full-table vacuums, so change is probably
unwarranted and could cause a worse regression (somewhere) than the
behaviour being fixed.

> In the longer term, we need to do something else. As the code now
> stands, reltuples is close to being completely untrustworthy: it will
> get updated only by VACUUM FULL or anti-wraparound vacuums, neither of
> which will happen often in a well-run installation. So the value will
> inevitably diverge from reality, perhaps arbitrarily far from reality.
> I wonder whether we shouldn't get rid of it altogether (and relpages
> too), and make the planner look to the counts maintained by the stats
> collector instead of using reltuples/relpages. The main objection I can
> think of to that is that turning off stats collection will no longer be
> a viable option ... but how many people do that anyway?

-1 for such radical change at this stage of release.

Perhaps we can recalc the new reltuples value by pro-rating the amount
of change against the previous value?

i.e. for fraction of table scanned by partial vacuum, F

new reltuples value = ((1-F) * old val)
+ (F * (new val from partial scan))

So when we scan whole table the existing behaviour is preserved.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support