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 C5F4E536AC; Fri, 25 Mar 2005 21:12:37 +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 64303-08; Fri, 25 Mar 2005 21:12:35 +0000 (GMT) Received: from outbound.mailhop.org (outbound.mailhop.org [63.208.196.171]) by svr1.postgresql.org (Postfix) with ESMTP id 65CCA536FD; Fri, 25 Mar 2005 21:12:33 +0000 (GMT) Received: from ool-4350c7ad.dyn.optonline.net ([67.80.199.173] helo=[192.168.0.66]) by outbound.mailhop.org with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.44) id 1DEw6c-0001vF-P8; Fri, 25 Mar 2005 16:12:34 -0500 Message-ID: <42447EBB.20905@zeut.net> Date: Fri, 25 Mar 2005 16:12:27 -0500 From: "Matthew T. O'Connor" Organization: Terrie O'Connor Realtors User-Agent: Mozilla Thunderbird 1.0RC1 (Windows/20041201) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-hackers@postgresql.org, Otto Blomqvist , pgsql-performance@postgresql.org Subject: Re: lazy_update_relstats considered harmful (was Re: [PERFORM] 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> <12121.1111782169@sss.pgh.pa.us> In-Reply-To: <12121.1111782169@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Mail-Handler: MailHop Outbound by DynDNS.org X-Originating-IP: 67.80.199.173 X-Report-Abuse-To: abuse@dyndns.org (see http://www.mailhop.org/outbound/abuse.html for abuse reporting information) X-MHO-User: zeut X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/909 X-Sequence-Number: 65711 Tom Lane wrote: >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? >