Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 8CCA11337B2F for ; Thu, 3 Feb 2011 23:48:40 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 50749-03 for ; Fri, 4 Feb 2011 03:48:33 +0000 (UTC) X-Greylist: delayed 00:18:47.300025 by SQLgrey-1.7.6 Received: from mail-09.primus.ca (smtp-noauth7.primus.ca [216.254.180.38]) by mail.postgresql.org (Postfix) with ESMTP id B97E1133740B for ; Thu, 3 Feb 2011 23:48:30 -0400 (AST) Received: from [206.191.44.11] (helo=bambi.pccyber.com) by mail-09.primus.ca with esmtp (Exim 4.72) (envelope-from ) id 1PlCMg-0003e5-0D; Thu, 03 Feb 2011 22:29:42 -0500 Received: from localhost (localhost.localdomain [127.0.0.1]) by bambi.pccyber.com (Postfix) with ESMTP id 6D9971B49D2; Thu, 3 Feb 2011 22:29:37 -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 5iltlC6SRUEL; Thu, 3 Feb 2011 22:29:36 -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 8F2A71B49CB; Thu, 3 Feb 2011 22:29:36 -0500 (EST) Message-ID: <4D4B7317.3070803@mark.mielke.cc> Date: Thu, 03 Feb 2011 22:31:35 -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: Conor Walsh CC: jd@commandprompt.com, Robert Haas , "pgsql-performance@postgresql.org" Subject: Does auto-analyze work on dirty writes? (was: Re: [HACKERS] Slow count(*) again...) References: <201102012247.p11Ml6u02682@momjian.us> <4D48914B.4050403@dunslane.net> <4D489AD1.1010105@vmsinfo.com> <4D499E55.4020107@vmsinfo.com> <4D4A7B69.2020704@gmail.com> <1296786810.18411.102.camel@jd-desktop> In-Reply-To: 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/159 X-Sequence-Number: 42297 On 02/03/2011 09:45 PM, Conor Walsh wrote: > My understanding is that auto-analyze will fire only after my > transaction is completed, because it is a seperate daemon. If I do > like so: > > BEGIN; > COPY ...; > -- Dangerously un-analyzed > SELECT complicated-stuff ...; > END; > > Auto-analyze does not benefit me, or might not because it won't fire > often enough. I agree that analyze is very fast, and it often seems > to me like the cost/benefit ratio suggests making auto-analyze even > more aggressive. The count discussion is boring. Nothing new there. But auto-analyze on dirty writes does interest me. :-) My understanding is: 1) Background daemon wakes up and checks whether a number of changes have happened to the database, irrelevant of transaction boundaries. 2) Background daemon analyzes a percentage of rows in the database for statistical data, irrelevant of row visibility. 3) Analyze is important for both visible rows and invisible rows, as plan execution is impacted by invisible rows. As long as they are part of the table, they may impact the queries performed against the table. 4) It doesn't matter if the invisible rows are invisible because they are not yet committed, or because they are not yet vacuumed. Would somebody in the know please confirm the above understanding for my own piece of mind? Thanks, mark -- Mark Mielke