Partial vacuum versus pg_class.reltuples

I complained a couple days ago that in HEAD, vacuum is putting
very bogus values into pg_class.reltuples for indexes:
http://archives.postgresql.org/pgsql-bugs/2009-06/msg00037.php

After looking through the code a bit, I've confirmed my prior guess that
this is caused by the partial-vacuum patch. The number that's being
used is the number of live tuples found on the pages that were actually
scanned. There's a kluge in there to keep from storing this number as
the relation's own reltuples, but the implications for indexes were not
considered. The index AMs generally assume that what they're told in
IndexVacuumInfo.num_heap_tuples can be trusted, and that's not true
anymore.

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.

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?

Another interesting question is why successive vacuums aren't causing
the index reltuples counts to go to zero. Shouldn't a partial vacuum
result in *all* pages of the relation being marked as not needing to
be examined by the next vacuum?

regards, tom lane

I wrote:
> Another interesting question is why successive vacuums aren't causing
> the index reltuples counts to go to zero. Shouldn't a partial vacuum
> result in *all* pages of the relation being marked as not needing to
> be examined by the next vacuum?

I figured out the reason for that: the first 32 pages of the table are
always scanned, even if the whole thing is frozen, because of the
SKIP_PAGES_THRESHOLD logic. We could change that behavior by
initializing all_visible_streak to SKIP_PAGES_THRESHOLD instead of zero.
But if we did so then having even just page zero be skippable would mean
that we clear scanned_all and thus fail to update reltuples, which is
probably not a good thing.

regards, tom lane

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

Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
> On Sat, 2009-06-06 at 15:44 -0400, Tom Lane wrote:
>> In the longer term, we need to do something else.

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

Uh, by "longer term" I meant this is something to think about for 8.5.

regards, tom lane

On Sat, Jun 6, 2009 at 3:44 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I complained a couple days ago that in HEAD, vacuum is putting
> very bogus values into pg_class.reltuples for indexes:
> http://archives.postgresql.org/pgsql-bugs/2009-06/msg00037.php
>
> After looking through the code a bit, I've confirmed my prior guess that
> this is caused by the partial-vacuum patch.  The number that's being
> used is the number of live tuples found on the pages that were actually
> scanned.  There's a kluge in there to keep from storing this number as
> the relation's own reltuples, but the implications for indexes were not
> considered.  The index AMs generally assume that what they're told in
> IndexVacuumInfo.num_heap_tuples can be trusted, and that's not true
> anymore.
>
> 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.

Am I wrong to be frightened by the implications of updating this value
only once in a blue moon? Doesn't this have the potential to result
in really bad plans? Do we have any reasonable manual way of forcing
VACUUM to scan the entire heap?

...Robert

On Sun, Jun 7, 2009 at 7:11 PM, Robert Haas<robertmhaas(at)gmail(dot)com> wrote:
> Am I wrong to be frightened by the implications of updating this value
> only once in a blue moon?  Doesn't this have the potential to result
> in really bad plans?  Do we have any reasonable manual way of forcing
> VACUUM to scan the entire heap?

It shouldn't be necessary to scan the entire heap to get a reasonable
estimate for the number of tuples. Analyze doesn't have to, for
example. Perhaps we should just make autovacuum periodically run
analyze even if it has run a vacuum recently -- i.e. not make vacuum
count as a recent analyze.

Actually it should be possible to get a more accurate estimate out of
ANALYZE than we used to as well. It could very quickly scan the entire
FSM and use that and the average tuple size to get a much more
accurate estimate for the number of tuples.

For VACUUM ANALYZE we could have it count the actual number of tuples
in the vacuumable pages and separately take a sample of non-vacuumable
pages and calculate an estimate based on the FSM and the average tuple
size in those non-vacuumable pages and add those two values together.
Since it just looked at every vacuumable page those FSM values are
precisely accurate and the estimate for average tuple size ought to be
pretty reliable.

--
greg
http://mit.edu/~gsstark/resume.pdf

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Am I wrong to be frightened by the implications of updating this value
> only once in a blue moon?

It's not great, but I think it's probably not catastrophic either.
Keep in mind that all we need from reltuples is that the ratio
reltuples/relpages be a reasonable estimate of the density of live
tuples, because what the planner actually uses is
GetRelationNumberOfBlocks() * reltuples / relpages.
So for example an append-only table isn't a big problem, even if it's
been quite a while since we updated reltuples and relpages.

There was some mention of having a partial vacuum extrapolate a value of
reltuples and update pg_class with that. I'm afraid that that could be
a seriously bad idea; because there is no very good reason to suppose
that the subset of recently-modified pages forms a good sample of the
whole table as far as live-tuple density goes.

[ 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.

So actually I think we are in better shape than I realized, so far as
the heap reltuples numbers go. The case that's a bit nasty is where
we are propagating the heap reltuples number to the index reltuples
number for a GIN index. (Remember this only matters for a partial
index.) As the code stands today, what we'll be propagating is the
reltuples estimate from the most recent ANALYZE, not the ANALYZE that
we might be about to conduct. This is not great; but considering that
we are completely ignoring the first-order problem of the partial index
predicate's selectivity, quibbling about a second-order effect like the
estimate being out of date is pretty pointless.

> Do we have any reasonable manual way of forcing
> VACUUM to scan the entire heap?

You could use VACUUM FREEZE, for instance.

regards, tom lane

On Sun, Jun 7, 2009 at 3:24 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Am I wrong to be frightened by the implications of updating this value
>> only once in a blue moon?
>
> It's not great, but I think it's probably not catastrophic either.
> Keep in mind that all we need from reltuples is that the ratio
> reltuples/relpages be a reasonable estimate of the density of live
> tuples, because what the planner actually uses is
>        GetRelationNumberOfBlocks() * reltuples / relpages.
> So for example an append-only table isn't a big problem, even if it's
> been quite a while since we updated reltuples and relpages.

My first reaction was to be relieved by this explanation, but on
further thought I'm not sure I was right to be relieved. The
frequency of anti-wraparound vacuums is so low that it doesn't seem
inconceivable that this ratio could change considerably on a far
shorter time scale. (For me, at least in some cases, it's less than
the frequency with which I dump+reload for maintenance reasons like OS
upgrade, PG upgrade, HW upgrade...)

> There was some mention of having a partial vacuum extrapolate a value of
> reltuples and update pg_class with that.  I'm afraid that that could be
> a seriously bad idea; because there is no very good reason to suppose
> that the subset of recently-modified pages forms a good sample of the
> whole table as far as live-tuple density goes.

I think you're right about that.

> [ 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.

> So actually I think we are in better shape than I realized, so far as
> the heap reltuples numbers go.  The case that's a bit nasty is where
> we are propagating the heap reltuples number to the index reltuples
> number for a GIN index.  (Remember this only matters for a partial
> index.)  As the code stands today, what we'll be propagating is the
> reltuples estimate from the most recent ANALYZE, not the ANALYZE that
> we might be about to conduct.  This is not great; but considering that
> we are completely ignoring the first-order problem of the partial index
> predicate's selectivity, quibbling about a second-order effect like the
> estimate being out of date is pretty pointless.

Being one ANALYZE behind shouldn't be too bad.

>> 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.

...Robert

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?

regards, tom lane

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

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> 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.

If anyone actually shows up with a clear problem, we can recommend
VACUUM FREEZE. I'm not going to panic about it right now.

regards, tom lane

Robert Haas escribió:

> 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.

IIRC you can set vacuum_freeze_table_age to 0.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Robert Haas escribi:
>> Maybe we should just have a GUC to enable/disable
>> partial vacuums.

> IIRC you can set vacuum_freeze_table_age to 0.

That has the same effects as issuing VACUUM FREEZE, no?

regards, tom lane

Tom Lane escribió:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > Robert Haas escribi:
> >> Maybe we should just have a GUC to enable/disable
> >> partial vacuums.
>
> > IIRC you can set vacuum_freeze_table_age to 0.
>
> That has the same effects as issuing VACUUM FREEZE, no?

As far as I can make from the docs, I think it only forces a full table
scan, but the freeze age remains the same.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Jun 8, 2009 at 10:40 AM, Alvaro
Herrera<alvherre(at)commandprompt(dot)com> wrote:
> Tom Lane escribió:
>> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> > Robert Haas escribió:
>> >> Maybe we should just have a GUC to enable/disable
>> >> partial vacuums.
>>
>> > IIRC you can set vacuum_freeze_table_age to 0.
>>
>> That has the same effects as issuing VACUUM FREEZE, no?
>
> As far as I can make from the docs, I think it only forces a full table
> scan, but the freeze age remains the same.

Yeah, that looks like what I was looking for, thanks. I looked for it
in the docs under the vacuum-related sections, but couldn't find it,
and I didn't know the name of it so I couldn't find it that way
either. I wonder if we should consider moving all of the vacuum and
autovacuum parameters into one section.

http://developer.postgresql.org/pgdocs/postgres/runtime-config-client.html#GUC-VACUUM-FREEZE-TABLE-AGE

In the worst case scenario where the new partial-table-vacuums are
causing headaches for someone, they should hopefully be able to use
this parameter to basically turn them off without too many nasty side
effects.

(Another nice thing about this parameter is that if you have a really
big table that is append-mostly, you can potentially tune this
parameter downward to spread out the freezing activity. With the
default settings, you might cruise along merrily until you hit 150M
transactions and then generate an I/O storm as you freeze a pretty big
chunk of the table all at once. With a lower setting, non-partial
vacuums will be more frequent, but each one will generate a smaller
amount of write traffic.)

...Robert

(back from vacation)

Tom Lane wrote:
> I wrote:
>> Another interesting question is why successive vacuums aren't causing
>> the index reltuples counts to go to zero. Shouldn't a partial vacuum
>> result in *all* pages of the relation being marked as not needing to
>> be examined by the next vacuum?
>
> I figured out the reason for that: the first 32 pages of the table are
> always scanned, even if the whole thing is frozen, because of the
> SKIP_PAGES_THRESHOLD logic. We could change that behavior by
> initializing all_visible_streak to SKIP_PAGES_THRESHOLD instead of zero.
> But if we did so then having even just page zero be skippable would mean
> that we clear scanned_all and thus fail to update reltuples, which is
> probably not a good thing.

Right, that's exactly why I wrote it like that. I also thought about
scanning the (beginning of the) visibility map first to see if there's
big enough gaps in there to warrant skipping pages, but went with the
current approach because it's so much simpler.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com