Re: Partial vacuum versus pg_class.reltuples - Mailing list pgsql-hackers

From Simon Riggs
Subject Re: Partial vacuum versus pg_class.reltuples
Date
Msg-id 1244394189.15799.59.camel@ebony.2ndQuadrant
Whole thread Raw
In response to Partial vacuum versus pg_class.reltuples  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Partial vacuum versus pg_class.reltuples
List pgsql-hackers
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.comPostgreSQL Training, Services and Support



pgsql-hackers by date:

Previous
From: Simon Riggs
Date:
Subject: Re: Revisiting default_statistics_target
Next
From: Tom Lane
Date:
Subject: Re: Partial vacuum versus pg_class.reltuples