From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Florian Helmberger <fh@25th-floor.com>
Cc: pgsql-hackers@postgresql.org, Alvaro Herrera <alvherre@commandprompt.com>
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
Date: Wed, 25 May 2011 13:04:27 -0400
Message-ID: <22926.1306343067@sss.pgh.pa.us> (raw)
In-Reply-To: <BANLkTim7YNLBBDUT9qXZrO2biuwDZ3kjyg@mail.gmail.com>
References: <4DD28F73.6010108@25th-floor.com>
<25017.1306291669@sss.pgh.pa.us>
<4DDCA801.70805@25th-floor.com>
<19738.1306338472@sss.pgh.pa.us>
<BANLkTi=973Arh-nXLO6YFP7_ZTCeNp-X7w@mail.gmail.com>
<22497.1306341699@sss.pgh.pa.us>
<BANLkTim7YNLBBDUT9qXZrO2biuwDZ3kjyg@mail.gmail.com>
Robert Haas <robertmhaas@gmail.com> writes:
> On Wed, May 25, 2011 at 12:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Yeah, I had been thinking about the latter point. We could be
>> conservative and just keep the reported tuple density the same (ie,
>> update relpages to the new correct value, while setting reltuples so
>> that the density ratio doesn't change). But that has its own problems
>> when the table contents *do* change. What I'm currently imagining is
>> to do a smoothed moving average, where we factor in the new density
>> estimate with a weight dependent on the percentage of the table we did
>> scan. That is, the calculation goes something like
>>
>> old_density = old_reltuples / old_relpages
>> new_density = counted_tuples / scanned_pages
>> reliability = scanned_pages / new_relpages
>> updated_density = old_density + (new_density - old_density) * reliability
>> new_reltuples = updated_density * new_relpages
>>
>> We could slow the moving-average convergence even further when
>> reliability is small; for instance if you were really paranoid you might
>> want to use the square of reliability in line 4. That might be
>> overdoing it, though.
> I don't know. That's maybe better, but I'd be willing to wager that
> in some cases it will just slow down the rate at which we converge to
> a completely incorrect value, while in other cases it'll fail to
> update the data when it really has changed.
[ shrug... ] When you don't have complete information, it's *always*
the case that you will sometimes make a mistake. That's not
justification for paralysis, especially not when the existing code is
demonstrably broken.
What occurs to me after thinking a bit more is that the existing tuple
density is likely to be only an estimate, too (one coming from the last
ANALYZE, which could very well have scanned even less of the table than
VACUUM did). So what I now think is that both VACUUM and ANALYZE ought
to use a calculation of the above form to arrive at a new value for
pg_class.reltuples. In both cases it would be pretty easy to track the
number of pages we looked at while counting tuples, so the same raw
information is available.
> I am wondering, though, why we're not just inserting a special-purpose
> hack for TOAST tables.
Because the problem is not specific to TOAST tables. As things
currently stand, we will accept the word of an ANALYZE as gospel even if
it scanned 1% of the table, and completely ignore the results from a
VACUUM even if it scanned 99% of the table. This is not sane.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, robertmhaas@gmail.com, fh@25th-floor.com, alvherre@commandprompt.com
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
In-Reply-To: <22926.1306343067@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox