X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B3262535E6; Fri, 25 Mar 2005 20:22:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90220-06; Fri, 25 Mar 2005 20:22:49 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id CAB6153608; Fri, 25 Mar 2005 20:22:48 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2PKMn9N012122; Fri, 25 Mar 2005 15:22:49 -0500 (EST) To: "Matthew T. O'Connor" Cc: pgsql-hackers@postgreSQL.org, Otto Blomqvist , pgsql-performance@postgreSQL.org Subject: lazy_update_relstats considered harmful (was Re: [PERFORM] pg_autovacuum not having enough suction ?) In-reply-to: <11991.1111780527@sss.pgh.pa.us> References: <42430FA0.9080204@zeut.net> <002b01c530c5$ad5a1620$e602a8c0@blomqvist> <42435408.30303@zeut.net> <00f201c53168$96903480$e602a8c0@blomqvist> <42446A66.6010504@zeut.net> <11991.1111780527@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Fri, 25 Mar 2005 14:55:27 -0500" Date: Fri, 25 Mar 2005 15:22:49 -0500 Message-ID: <12121.1111782169@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/905 X-Sequence-Number: 65707 I wrote: > One thing that is possibly relevant here is that in 8.0 a plain VACUUM > doesn't set reltuples to the exactly correct number, but to an > interpolated value that reflects our estimate of the "steady state" > average between vacuums. I wonder if that code is wrong, or if it's > operating as designed but is confusing autovac. Now that I think it over, I'm thinking that I must have been suffering severe brain fade the day I wrote lazy_update_relstats() (see vacuumlazy.c). The numbers that that routine is averaging are the pre- and post-vacuum physical tuple counts. But the difference between them consists of known-dead tuples, and we shouldn't be factoring dead tuples into reltuples. The planner has always considered reltuples to count only live tuples, and I think this is correct on two grounds: 1. The numbers of tuples estimated to be returned by scans certainly shouldn't count dead ones. 2. Dead tuples don't have that much influence on scan costs either, at least not once they are marked as known-dead. Certainly they shouldn't be charged at full freight. It's possible that there'd be some value in adding a column to pg_class to record dead tuple count, but given what we have now, the calculation in lazy_update_relstats is totally wrong. The idea I was trying to capture is that the tuple density is at a minimum right after VACUUM, and will increase as free space is filled in until the next VACUUM, so that recording the exact tuple count underestimates the number of tuples that will be seen on-the-average. But I'm not sure that idea really holds water. The only way that a table can be at "steady state" over a long period is if the number of live tuples remains roughly constant (ie, inserts balance deletes). What actually increases and decreases over a VACUUM cycle is the density of *dead* tuples ... but per the above arguments this isn't something we should adjust reltuples for. So I'm thinking lazy_update_relstats should be ripped out and we should go back to recording just the actual stats. Sound reasonable? Or was I right the first time and suffering brain fade today? regards, tom lane