Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 9C9329FB1BF for ; Sat, 23 Sep 2006 10:57:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 81646-03 for ; Sat, 23 Sep 2006 10:57:26 -0300 (ADT) X-Greylist: delayed 00:08:09.92354 by SQLgrey- Received: from smtp0.libero.it (smtp0.libero.it [193.70.192.33]) by postgresql.org (Postfix) with ESMTP id CCA389FA5CE for ; Sat, 23 Sep 2006 10:57:25 -0300 (ADT) Received: from localhost (172.16.1.177) by smtp0.libero.it (7.0.027-DD01) id 44EEFA2A02083E3F; Sat, 23 Sep 2006 15:49:13 +0200 Received: from smtp0.libero.it ([172.16.1.204]) by localhost (asav0.libero.it [193.70.192.9]) (amavisd-new, port 10024) with ESMTP id 28173-06; Sat, 23 Sep 2006 15:49:13 +0200 (CEST) Received: from [151.25.98.251] (ppp-251-98.25-151.libero.it [151.25.98.251]) by smtp0.libero.it (Postfix) with ESMTP id 3480F33389E; Sat, 23 Sep 2006 15:49:11 +0200 (MEST) Message-ID: <45144C10.2070501@streppone.it> Date: Fri, 22 Sep 2006 22:48:16 +0200 From: Cosimo Streppone User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317) X-Accept-Language: it, it-it, en-us, en MIME-Version: 1.0 To: C Storm CC: Postgresql Performance list Subject: Re: Update on high concurrency OLTP application and Postgres References: <45110543.6020501@streppone.it> <20060920110731.GB18219@phlogiston.dyndns.org> <45113286.2090204@streppone.it> <1158943002.460558.40420@b28g2000cwb.googlegroups.com> In-Reply-To: <1158943002.460558.40420@b28g2000cwb.googlegroups.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Scanned: with antispam and antivirus automated system at libero.it X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.247 tagged_above=0 required=5 tests=DATE_IN_PAST_12_24 X-Spam-Level: * X-Archive-Number: 200609/320 X-Sequence-Number: 21006 Christian Storm wrote: >>At the moment, my rule of thumb is to check out the ANALYZE VERBOSE >>messages to see if all table pages are being scanned. >> >> INFO: "mytable": scanned xxx of yyy pages, containing ... >> >>If xxx = yyy, then I keep statistics at the current level. >>When xxx is way less than yyy, I increase the numbers a bit >>and retry. >> >>It's probably primitive, but it seems to work well. > > What heuristic do you use to up the statistics for such a table? No heuristics, just try and see. For tables of ~ 10k pages, I set statistics to 100/200. For ~ 100k pages, I set them to 500 or more. I don't know the exact relation. > Once you've changed it, what metric do you use to > see if it helps or was effective? I rerun an analyze and see the results... :-) If you mean checking the usefulness, I can see it only under heavy load, if particular db queries run in the order of a few milliseconds. If I see normal queries that take longer and longer, or they even appear in the server's log (> 500 ms), then I know an analyze is needed, or statistics should be set higher. -- Cosimo