Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 0F3F51337B94 for ; Fri, 4 Feb 2011 21:48:36 -0400 (AST) 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 88857-03 for ; Sat, 5 Feb 2011 01:48:30 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-01.primus.ca (smtp-noauth7.primus.ca [216.254.180.38]) by mail.postgresql.org (Postfix) with ESMTP id 50B6F1337B7E for ; Fri, 4 Feb 2011 21:48:27 -0400 (AST) Received: from [206.191.44.11] (helo=bambi.pccyber.com) by mail-01.primus.ca with esmtp (Exim 4.72) (envelope-from ) id 1PlXGD-00008J-2J; Fri, 04 Feb 2011 20:48:25 -0500 Received: from localhost (localhost.localdomain [127.0.0.1]) by bambi.pccyber.com (Postfix) with ESMTP id B77BF1B49D2; Fri, 4 Feb 2011 20:48:22 -0500 (EST) X-Virus-Scanned: amavisd-new at pccyber.com Received: from bambi.pccyber.com ([127.0.0.1]) by localhost (bambi.pccyber.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Mtsc2qphqAYN; Fri, 4 Feb 2011 20:48:22 -0500 (EST) Received: from [173.35.103.0] (CPE0050fca9b0e1-CM0018c0d3a5ba.cpe.net.cable.rogers.com [173.35.103.0]) (Authenticated sender: mark@pccyber.com) by bambi.pccyber.com (Postfix) with ESMTPA id CA3101B49CB; Fri, 4 Feb 2011 20:48:21 -0500 (EST) Message-ID: <4D4CACD5.7060104@mark.mielke.cc> Date: Fri, 04 Feb 2011 20:50:13 -0500 From: Mark Mielke User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.13) Gecko/20101209 Fedora/3.1.7-0.35.b3pre.fc14 Thunderbird/3.1.7 MIME-Version: 1.0 To: Tom Lane CC: Conor Walsh , jd@commandprompt.com, Robert Haas , "pgsql-performance@postgresql.org" Subject: Re: Does auto-analyze work on dirty writes? References: <201102012247.p11Ml6u02682@momjian.us> <4D499E55.4020107@vmsinfo.com> <4D4A7B69.2020704@gmail.com> <1296786810.18411.102.camel@jd-desktop> <4D4B7317.3070803@mark.mielke.cc> <22836.1296834064@sss.pgh.pa.us> In-Reply-To: <22836.1296834064@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201102/230 X-Sequence-Number: 42368 On 02/04/2011 10:41 AM, Tom Lane wrote: > 1. Autovacuum fires when the stats collector's insert/update/delete > counts have reached appropriate thresholds. Those counts are > accumulated from messages sent by backends at transaction commit or > rollback, so they take no account of what's been done by transactions > still in progress. > > 2. Only live rows are included in the stats computed by ANALYZE. > (IIRC it uses SnapshotNow to decide whether rows are live.) > > Although the stats collector does track an estimate of the number of > dead rows for the benefit of autovacuum, this isn't used by planning. > Table bloat is accounted for only in terms of growth of the physical > size of the table in blocks. Thanks, Tom. Does this un-analyzed "bloat" not impact queries? I guess the worst case here is if autovaccum is disabled for some reason and 99% of the table is dead rows. If I understand the above correctly, I think analyze might generate a bad plan under this scenario, thinking that a value is unique, using the index - but every tuple in the index has the same value and each has to be looked up in the table to see if it is visible? Still, I guess the idea here is not to disable autovacuum, making dead rows insignificant in the grand scheme of things. I haven't specifically noticed any performance problems here - PostgreSQL is working great for me as usual. Just curiosity... Cheers, mark -- Mark Mielke