Re: Partial vacuum versus pg_class.reltuples

On Sun, Jun 7, 2009 at 4:19 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Sun, Jun 7, 2009 at 3:24 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> [ thinks a bit and reads the code some more ... ]  There is a
>>> considerably safer alternative, which is to let ANALYZE update the
>>> reltuples estimate based on the pages it sampled; which should be a
>>> considerably less biased sample than the pages a partial vacuum would
>>> have looked at.  And we have already got the code doing that, either
>>> in a standalone ANALYZE or an ANALYZE attached to a vacuum that turned
>>> out to be partial.
>
>> I'm confused with how this squares with the previous discussion - I
>> thought you observed a case where this wasn't happening.
>
> No, the problem was that the wrong things were getting done to reltuples
> entries for indexes.  The heap reltuples values were correct, but there
> is code that takes the heap value into account when estimating the value
> for an index, and that was all wet because it was using the number of
> tuples on the scanned pages rather than any total-table estimate.
>
>>>> Do we have any reasonable manual way of forcing
>>>> VACUUM to scan the entire heap?
>>>
>>> You could use VACUUM FREEZE, for instance.
>
>> That'll generate a fair amount of I/O.
>
> Er, isn't that exactly what you asked for?

Basically, I'm trying to figure out what we're going to recommend to
someone who gets bitten by whatever remaining corner case still exists
after your recent patch, and I admit I'm not real clear on what that
is. VACUUM FULL doesn't seem like a good solution because it's more
than just "vacuum but don't skip any pages even if the visibility map
says you can". Maybe we should just have a GUC to enable/disable
partial vacuums.

...Robert