Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id B7FC99FB2B4 for ; Thu, 18 Jan 2007 15:20:25 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 23194-02 for ; Thu, 18 Jan 2007 15:20:13 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from outbound.mailhop.org (outbound.mailhop.org [63.208.196.171]) by postgresql.org (Postfix) with ESMTP id 03D4C9FA1D8 for ; Thu, 18 Jan 2007 15:20:13 -0400 (AST) Received: from ool-435121e8.dyn.optonline.net ([67.81.33.232] helo=[192.168.0.96]) by outbound.mailhop.org with esmtpa (Exim 4.63) (envelope-from ) id 1H7co0-0003x7-EN; Thu, 18 Jan 2007 14:20:12 -0500 X-Mail-Handler: MailHop Outbound by DynDNS X-Originating-IP: 67.81.33.232 X-Report-Abuse-To: abuse@dyndns.com (see http://www.mailhop.org/outbound/abuse.html for abuse reporting information) X-MHO-User: tocr Message-ID: <45AFC86B.9010704@zeut.net> Date: Thu, 18 Jan 2007 14:20:11 -0500 From: "Matthew T. O'Connor" User-Agent: Thunderbird 2.0b1 (Windows/20061206) MIME-Version: 1.0 To: Jeremy Haile CC: pgsql-performance@postgresql.org Subject: Re: Autoanalyze settings with zero scale factor References: <1169145474.20103.1169962769@webmail.messagingengine.com> In-Reply-To: <1169145474.20103.1169962769@webmail.messagingengine.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/315 X-Sequence-Number: 22916 Jeremy Haile wrote: > I changed the table-specific settings so that the ANALYZE base threshold > was 5000 and the ANALYZE scale factor is 0. According to the documented > formula: analyze threshold = analyze base threshold + analyze scale > factor * number of tuples, I assumed that this would cause the table to > be analyzed everytime 5000 tuples were inserted/updated/deleted. That is right, and exactly how the scaling factor / base value are supposed to work, so this should be fine. > However, the tables have been updated with tens of thousands of inserts > and the table has still not been analyzed (according to > pg_stat_user_tables). Does a scale factor of 0 cause the table to never > be analyzed? What am I doing wrong? I'm using PG 8.2.1. No a scaling factor of 0 shouldn't stop the table from being analyzed. Unless it's just a bug, my only guess is that autovacuum may be getting busy at times (vacuuming large tables for example) and hasn't had a chance to even look at that table for a while, and by the time it gets to it, there have been tens of thousands of inserts. Does that sounds plausible? Also, are other auto-vacuums and auto-analyzes showing up in the pg_stats table? Maybe it's a stats system issue.