From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Florian Helmberger <fh@25th-floor.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Greg Stark <gsstark@mit.edu>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
Date: Thu, 26 May 2011 17:50:00 -0400
Message-ID: <9007.1306446600@sss.pgh.pa.us> (raw)
In-Reply-To: <4DDE7EB8020000250003DDA8@gw.wicourts.gov>
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>
<BANLkTi=jcKSU5zR+JG2BvfLNCtezC9sb+g@mail.gmail.com>
<15043.1306423500@sss.pgh.pa.us>
<BANLkTikrCaoZCVrda6SNCUQFiYvvZX75BA@mail.gmail.com>
<25097.1306426982@sss.pgh.pa.us>
<BANLkTikxM6V5DUsXnDEs84z=oRMbyeoOgA@mail.gmail.com>
<4DDE475B020000250003DD63@gw.wicourts.gov>
<BANLkTikTbZ8gibj+X++vkdqkxxJCU6trWw@mail.gmail.com>
<4DDE5033020000250003DD71@gw.wicourts.gov>
<BANLkTikGu30z1rwYeN0NwuO8jMFWHzus+Q@mail.gmail.com>
<4DDE7EB8020000250003DDA8@gw.wicourts.gov>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> When we prune or vacuum a page, I don't suppose we have enough
> information about that page's previous state to calculate a tuple
> count delta, do we? That would allow a far more accurate number to
> be maintained than anything suggested so far, as long as we tweak
> autovacuum to count inserts toward the need to vacuum.
Well, that was the other direction that was suggested upthread: stop
relying on reltuples at all, but use the stats collector's counts.
That might be a good solution in the long run, but there are some
issues:
1. It's not clear how using a current count, as opposed to
time-of-last-vacuum count, would affect the behavior of the autovacuum
control logic. At first glance I think it would break it, since the
basic logic there is "how much of the table changed since it was last
vacuumed?". Even if the equations could be modified to still work,
I remember enough feedback control theory from undergrad EE to think that
this is something to be seriously scared of tweaking without extensive
testing. IMO it is far more risky than what Robert is worried about.
2. You still have the problem that we're exposing inaccurate (or at
least less accurate than they could be) counts to the planner and to
onlooker clients. We could change the planner to also depend on the
stats collector instead of reltuples, but at that point you just removed
the option for people to turn off the stats collector. The implications
for plan stability might be unpleasant, too.
So that's not a direction I want to go without a significant amount
of work and testing.
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, Kevin.Grittner@wicourts.gov, robertmhaas@gmail.com, fh@25th-floor.com, alvherre@commandprompt.com, gsstark@mit.edu, pgsql-hackers@postgresql.org
Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum
In-Reply-To: <9007.1306446600@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