X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 16CEF9DCCC8 for ; Wed, 15 Mar 2006 19:11:59 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 74245-03 for ; Wed, 15 Mar 2006 19:12:01 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.201]) by postgresql.org (Postfix) with ESMTP id E4CAC9DCBF9 for ; Wed, 15 Mar 2006 19:11:55 -0400 (AST) Received: by zproxy.gmail.com with SMTP id 14so233980nzn for ; Wed, 15 Mar 2006 15:11:58 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:user-agent:x-accept-language:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=hvzDfF7V0gBXba342WKAcJj4KenrKSpHf/pS8hT+gcGFO7exQNrCWygXfhaQkL35pnxTL8dIkruAqcgX5zU+5R6ktz9RzZkhgN6EXzSxqsvqPQYCASoadJRJ0hmjg86xpUCFmNFbK6fDu1zss6kGLpBypMua9TWIv8yl1KYWeDU= Received: by 10.37.14.63 with SMTP id r63mr1472378nzi; Wed, 15 Mar 2006 15:11:58 -0800 (PST) Received: from ?10.1.1.20? ( [203.217.18.65]) by mx.gmail.com with ESMTP id 6sm1614347nzn.2006.03.15.15.11.56; Wed, 15 Mar 2006 15:11:58 -0800 (PST) Message-ID: <44189F3B.1020209@gmail.com> Date: Thu, 16 Mar 2006 10:11:55 +1100 From: Chris User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jan de Visser CC: pgsql-performance@postgresql.org Subject: Re: Slow SELECTS after large update cycle References: <200603151439.13958.jdevisser@digitalfairway.com> In-Reply-To: <200603151439.13958.jdevisser@digitalfairway.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.118 required=5 tests=[AWL=0.118] X-Spam-Score: 0.118 X-Spam-Level: X-Archive-Number: 200603/223 X-Sequence-Number: 17660 Jan de Visser wrote: > Hello, > > After fixing the hanging problems I reported here earlier (by uninstalling > W2K3 SP1), I'm running into another weird one. > > After doing a +/- 8hr cycle of updates and inserts (what we call a 'batch'), > the first 'reporting' type query on tables involved in that write cycle is > very slow. As an example, I have a query which according to EXPLAIN ANALYZE > takes about 1.1s taking 46s. After this one hit, everything is back to > normal, and subsequent executions of the same query are in fact subsecond. > Restarting the appserver and pgsql does not make the slowness re-appear, only > running another batch will. > > During the 'write'/batch cycle, a large number of rows in various tables are > inserted and subsequently (repeatedly) updated. The reporting type queries > after that are basically searches on those tables. After a large batch you need to run 'analyze' over the tables involved to get postgresql to update it's statistics so it can work out which indexes etc it should use. -- Postgresql & php tutorials http://www.designmagick.com/