Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id E82DFB5DBF7 for ; Fri, 27 May 2011 12:07:15 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 72423-10 for ; Fri, 27 May 2011 15:07:07 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pw0-f46.google.com (mail-pw0-f46.google.com [209.85.160.46]) by mail.postgresql.org (Postfix) with ESMTP id 859E2B5DBF0 for ; Fri, 27 May 2011 12:07:07 -0300 (ADT) Received: by pwi15 with SMTP id 15so839650pwi.19 for ; Fri, 27 May 2011 08:07:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=9VDQYU19DKxfPcT3mP75LmaV62ZQOxssvqhT3XPCnhM=; b=R8F49004y+5teowYBHGODN/ULsqxeEl6AnWUKISqImXrqqn0YnkoQHMzrkbaydzDFk r81lslFouVVSDgTEaMvp9HOwS1VcFZXv9eCaSTcsR5erH8Ion4LceAQkso8BBS1ydgJ6 TEljyhFRXYS+nRfPNvGOUn7BNCbM4AkQRaL0w= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=ugREprfnFtWE+Y13/zOXG3kJxXH5ZU70D/W968etofzYUzlDNeeOO05gu/vpYEO16+ nWGLfWRfJWsuG4A/GlxvYYWuTVTF6ZFQMWzcyBnPNBSbslPodayhjVBvffXcdaq2NLLw txPD7TrAm3e9pT1Y+MAOnL6IXDz+4KaxPdBeU= MIME-Version: 1.0 Received: by 10.68.62.198 with SMTP id a6mr817240pbs.508.1306508826319; Fri, 27 May 2011 08:07:06 -0700 (PDT) Received: by 10.68.59.228 with HTTP; Fri, 27 May 2011 08:07:05 -0700 (PDT) In-Reply-To: <9007.1306446600@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> <22497.1306341699@sss.pgh.pa.us> <15043.1306423500@sss.pgh.pa.us> <25097.1306426982@sss.pgh.pa.us> <4DDE475B020000250003DD63@gw.wicourts.gov> <4DDE5033020000250003DD71@gw.wicourts.gov> <4DDE7EB8020000250003DDA8@gw.wicourts.gov> <9007.1306446600@sss.pgh.pa.us> Date: Fri, 27 May 2011 11:07:05 -0400 Message-ID: Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum From: Robert Haas To: Tom Lane Cc: Kevin Grittner , Florian Helmberger , Alvaro Herrera , Greg Stark , pgsql-hackers@postgresql.org Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.898 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RFC_ABUSE_POST=0.001 X-Spam-Level: X-Archive-Number: 201105/1418 X-Sequence-Number: 188452 On Thu, May 26, 2011 at 5:50 PM, Tom Lane wrote: > "Kevin Grittner" 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? =A0That 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. =A0At 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?". =A0Even 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. =A0IMO it is far more risky than what Robert is worried about. Yeah, I think that would be broken. > 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. =A0We 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. =A0The implication= s > 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. FWIW, I agree. Your proposed solution is certainly better than trying to do this; but it still seems a bit shaky to me. Still, maybe we don't have a better option. If it were me, I'd add an additional safety valve: use your formula if the percentage of the relation scanned is above some threshold where there's unlikely to be too much skew. But if the percentage scanned is too small, then don't use that formula. Instead, only update relpages/reltuples if the relation is now larger; set relpages to the new actual value, and scale up reltuples proportionately. However, I just work here. It's possible that I'm worrying about a problem that won't materialize in practice. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company