Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id E30DFF6332B for ; Thu, 19 Jul 2012 09:39:25 -0300 (ADT) Received: from relay4-d.mail.gandi.net ([217.70.183.196]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Srq0l-0005cP-QJ for pgsql-performance@postgresql.org; Thu, 19 Jul 2012 12:39:24 +0000 X-Originating-IP: 217.70.178.142 Received: from mfilter14-d.gandi.net (mfilter14-d.gandi.net [217.70.178.142]) by relay4-d.mail.gandi.net (Postfix) with ESMTP id B0ABF17209F; Thu, 19 Jul 2012 14:38:56 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at mfilter14-d.gandi.net Received: from relay4-d.mail.gandi.net ([217.70.183.196]) by mfilter14-d.gandi.net (mfilter14-d.gandi.net [10.0.15.180]) (amavisd-new, port 10024) with ESMTP id 60WDCt3VqD2b; Thu, 19 Jul 2012 14:38:55 +0200 (CEST) X-Originating-IP: 98.27.54.176 Received: from [192.168.10.146] (cpe-098-027-054-176.nc.res.rr.com [98.27.54.176]) (Authenticated sender: adsend@dunslane.net) by relay4-d.mail.gandi.net (Postfix) with ESMTPSA id DADA9172089; Thu, 19 Jul 2012 14:38:54 +0200 (CEST) Message-ID: <5007FFDD.5050103@dunslane.net> Date: Thu, 19 Jul 2012 08:38:53 -0400 From: Andrew Dunstan User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:13.0) Gecko/20120605 Thunderbird/13.0 MIME-Version: 1.0 To: Felix Scheicher CC: pgsql-performance@postgresql.org Subject: Re: queries are fast after dump->restore but slow again after some days dispite vacuum References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201207/233 X-Sequence-Number: 47414 On 07/19/2012 07:33 AM, Felix Scheicher wrote: > Hi, I am running a 9.1 server at Ubuntu. When I upgraded to the current version > I did a pg_dump followed by pg_restore and found that the db was much faster. > But slowed down again after two days. I did the dump-restore again and could now > compare the two (actually identical) databases. This is a query of the old one > directly after a VACUUM ANALYSE: ... > > No matter how much I vacuum or analyse the slow db, I don't get it faster. > I also checked for dead tuples - there are none. Try running CLUSTER on the relevant tables and see if it makes a difference. If it does you might want to look into using pg_reorg periodically. cheers andrew