Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 175E4B5DC0A for ; Wed, 25 May 2011 13:24:06 -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 90556-06 for ; Wed, 25 May 2011 16:23:59 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id C757CB5DBDE for ; Wed, 25 May 2011 13:23:58 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id p4PGNu9D022174; Wed, 25 May 2011 12:23:56 -0400 (EDT) To: Alvaro Herrera cc: Florian Helmberger , pgsql-hackers Subject: Re: [ADMIN] pg_class reltuples/relpages not updated by autovacuum/vacuum In-reply-to: <1306339028-sup-6948@alvh.no-ip.org> References: <4DD28F73.6010108@25th-floor.com> <25017.1306291669@sss.pgh.pa.us> <4DDCA801.70805@25th-floor.com> <19738.1306338472@sss.pgh.pa.us> <1306339028-sup-6948@alvh.no-ip.org> Comments: In-reply-to Alvaro Herrera message dated "Wed, 25 May 2011 12:00:38 -0400" Date: Wed, 25 May 2011 12:23:56 -0400 Message-ID: <22173.1306340636@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-5 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201105/1272 X-Sequence-Number: 188306 Alvaro Herrera writes: > Excerpts from Tom Lane's message of mié may 25 11:47:52 -0400 2011: >> I can see two basic approaches we might take here: >> >> 1. Modify autovacuum to use something from the stats collector, rather >> than reltuples, to make its decisions. I'm not too clear on why >> reltuples is being used there anyway; is there some good algorithmic or >> statistical reason why AV should be looking at a number from the last >> vacuum? > It uses reltuples simply because it was what the original contrib code > was using. Since pgstat was considerably weaker at the time, reltuples > might have been the only thing available. It's certainly the case that > pgstat has improved a lot since autovacuum got in, and some things have > been revised but not this one. On reflection I'm hesitant to do this, especially for a backpatched bug fix, because it would be changing the feedback loop behavior for autovacuum scheduling. That could have surprising consequences. >> 2. Revise the vacuum code so that it doesn't skip updating the pg_class >> entries. We could have it count the number of pages it skipped, rather >> than just keeping a bool, and then scale up the rel_tuples count to be >> approximately right by assuming the skipped pages have tuple density >> similar to the scanned ones. > Hmm, interesting idea. This would be done only for toast tables, or all > tables? I'm thinking just do it for all. The fact that these numbers don't necessarily update after a vacuum is certainly surprising in and of itself, and it did not work that way before the VM patch went in. I'm concerned about other stuff besides AV not dealing well with obsolete values. > At this point I only wonder why we store tuples & pages rather than just > live tuple density. It's just for backwards compatibility. I've thought about doing that in the past, but I don't know what client-side code might be looking at relpages/reltuples. It's not like collapsing them into one field would save much, anyway. regards, tom lane