From: Robert Haas <robertmhaas@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
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 12:54:28 -0400
Message-ID: <BANLkTim7YNLBBDUT9qXZrO2biuwDZ3kjyg@mail.gmail.com> (raw)
In-Reply-To: <22497.1306341699@sss.pgh.pa.us>
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>
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.
I am wondering, though, why we're not just inserting a special-purpose
hack for TOAST tables. Your email seems to indicate that regular
tables are already handled well enough, and certainly if we only whack
around the TOAST behavior it's much less likely to fry anything.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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: robertmhaas@gmail.com, tgl@sss.pgh.pa.us, fh@25th-floor.com, alvherre@commandprompt.com
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
In-Reply-To: <BANLkTim7YNLBBDUT9qXZrO2biuwDZ3kjyg@mail.gmail.com>
* 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