Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id E9210B5DC09 for ; Thu, 26 May 2011 14:50:53 -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 27205-08 for ; Thu, 26 May 2011 17:50:46 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gw0-f46.google.com (mail-gw0-f46.google.com [74.125.83.46]) by mail.postgresql.org (Postfix) with ESMTP id 5B305B5D832 for ; Thu, 26 May 2011 14:50:46 -0300 (ADT) Received: by gwaa18 with SMTP id a18so383776gwa.19 for ; Thu, 26 May 2011 10:50:46 -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=Dz+fpmBKIBqQyyA/tBLJdoyBHO6+74iCCcTyrkZrXDs=; b=oqscsz4KSj+LHQzukQItO+o6EC3fbZ2ZQEL//gVX7P7GViTT5hlxpKFOzUj61CFbYW mejyzK8U48GkEn4mRb3NOxPNnZ5OzHmFTFU1v8z9uoDQuQ/xyuz/4W24V8cPe3g5SOKk /yKWXttgpIxQPIMgvYCWcm+Qt/QzkZuiQGjc4= 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=Qf3RhT0sTeotH+D9R5NmpvkTLbNr3eO68Y+WyGErjGv9lWGytRWjc57NE8Sj7qCToE hce/wDEeu2NkDui5titGXb64ZS+J93B6r51UDPtW7HmIWyynBVUz75MZR/lw4P0auA9J dY/5pAnAd7GmivgZW4l4G0hG2sJA6mFl7rsp0= MIME-Version: 1.0 Received: by 10.236.161.131 with SMTP id w3mr1675108yhk.5.1306432245810; Thu, 26 May 2011 10:50:45 -0700 (PDT) Received: by 10.236.63.209 with HTTP; Thu, 26 May 2011 10:50:45 -0700 (PDT) In-Reply-To: <4DDE475B020000250003DD63@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> <22497.1306341699@sss.pgh.pa.us> <15043.1306423500@sss.pgh.pa.us> <25097.1306426982@sss.pgh.pa.us> <4DDE475B020000250003DD63@gw.wicourts.gov> Date: Thu, 26 May 2011 13:50:45 -0400 Message-ID: Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum From: Robert Haas To: Kevin Grittner Cc: Tom Lane , 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/1366 X-Sequence-Number: 188400 On Thu, May 26, 2011 at 1:28 PM, Kevin Grittner wrote: > Robert Haas wrote: >> I think we should really consider replacing reltuples with >> reltupledensity at some point. =A0I continue to be afraid that using >> a decaying average in this case is going to end up overweighting >> the values from some portion of the table that's getting scanned >> repeatedly, at the expense of other portions of the table that are >> not getting scanned at all. =A0Now, perhaps experience will prove >> that's not a problem. =A0But storing relpages and reltupledensity >> separately would give us more flexibility, because we could feel >> free to bump relpages even when we're not sure what to do about >> reltupledensity. =A0That would help Florian's problem quite a lot, >> even if we did nothing else. > > Given how trivial it would be to adjust reltuples to keep its ratio > to relpages about the same when we don't have a new "hard" number, > but some evidence that we should fudge our previous value, I don't > see where this change buys us much. =A0It seems to mostly obfuscate > the fact that we're changing our assumption about how many tuples we > have. =A0I would rather that we did that explicitly with code comments > about why it's justified than to slip it in the way you suggest. I'm a bit confused by this - what the current design obfuscates is the fact that reltuples and relpages are not really independent columns; you can't update one without updating the other, unless you want screwy behavior. Replacing reltuples by reltupledensity would fix that problem - it would be logical and non-damaging to update either column independently. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company