X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C49AD4758F1 for ; Fri, 2 May 2003 07:27:10 -0400 (EDT) Received: from matrix.gatewaynet.com (unknown [217.19.69.50]) by postgresql.org (Postfix) with ESMTP id D452F474E4F for ; Fri, 2 May 2003 07:27:08 -0400 (EDT) Received: from matrix.gatewaynet.com (localhost [127.0.0.1]) by matrix.gatewaynet.com (8.12.5/8.12.5) with ESMTP id h42GTb1s019424; Fri, 2 May 2003 14:29:37 -0200 Received: from localhost (achill@localhost) by matrix.gatewaynet.com (8.12.5/8.12.5/Submit) with ESMTP id h42GTYH0019420; Fri, 2 May 2003 14:29:35 -0200 Date: Fri, 2 May 2003 14:29:34 -0200 (GMT+2) From: Achilleus Mantzios To: Tom Lane Cc: Ron Mayer , , , Achilleus Mantzios Subject: Re: [SQL] 7.3 analyze & vacuum analyze problem In-Reply-To: <25282.1051747838@sss.pgh.pa.us> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Spam-Status: No, hits=-38.6 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, QUOTE_TWICE_1,REPLY_WITH_QUOTES,SIGNATURE_LONG_DENSE, USER_AGENT_PINE autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/2 X-Sequence-Number: 1781 On Wed, 30 Apr 2003, Tom Lane wrote: > "Ron Mayer" writes: > > Short summary: Later in the thread Tom explained my problem as free > > space not being evenly distributed across the table so ANALYZE's > > sampling gave skewed results. In my case, "pgstatuple" was a > > good tool for diagnosing the problem, "vacuum full" fixed my table > > and a much larger fsm_* would have probably prevented it. > > Not sure if that is Achilleus' problem or not. IIRC, there should be > no difference at all in what VACUUM ANALYZE and ANALYZE put into > pg_statistic (modulo random sampling variations of course). The only > difference is that VACUUM ANALYZE puts an exact tuple count into > pg_class.reltuples (since the VACUUM part groveled over every tuple, > this info is available) whereas ANALYZE does not scan the entire table > and so has to put an estimate into pg_class.reltuples. > > It would be interesting to see the pg_class and pg_stats rows for this > table after VACUUM ANALYZE and after ANALYZE --- but I suspect the main > difference will be the reltuples values. Unfortunately i did a VACUUM FULL, and later a dump/reload which eliminated (vanished) the problem regarding the difference between plain ANALYZE and VACUUM ANALYZE. However, now the condition is much more wierd, in the sense that after the reload, some planner costs seem too low (~ 6) the expected number of rows is very often 1, and the correct index is used, resulting in a ultra speed situation (that i never had expected!). After vacuum full analyze, or vacuum analyze things get slow again. I surely must generate a reproducable scenario, describing the exact steps made, so i'll focus on that. In the meantime if Tom or some other hacker has any ideas that would be great. > > regards, tom lane > -- ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-210-8981112 fax: +30-210-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr