Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id 5208DF6332B
	for <pgsql-performance@postgresql.org>;
	Thu, 19 Jul 2012 12:49:14 -0300 (ADT)
Received: from relay4-d.mail.gandi.net ([217.70.183.196])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <andrew@dunslane.net>) id 1SrsyV-0001El-TA
	for pgsql-performance@postgresql.org; Thu, 19 Jul 2012 15:49:13 +0000
X-Originating-IP: 217.70.178.145
Received: from mfilter17-d.gandi.net (mfilter17-d.gandi.net [217.70.178.145])
	by relay4-d.mail.gandi.net (Postfix) with ESMTP id 6D0C81720CB;
	Thu, 19 Jul 2012 17:48:49 +0200 (CEST)
X-Virus-Scanned: Debian amavisd-new at mfilter17-d.gandi.net
Received: from relay4-d.mail.gandi.net ([217.70.183.196])
	by mfilter17-d.gandi.net (mfilter17-d.gandi.net [10.0.15.180])
	(amavisd-new, port 10024)
	with ESMTP id f6k1N10FS+Dn; Thu, 19 Jul 2012 17:48:48 +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 9A9941720BF;
	Thu, 19 Jul 2012 17:48:47 +0200 (CEST)
Message-ID: <50082C5E.6020606@dunslane.net>
Date: Thu, 19 Jul 2012 11:48:46 -0400
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:13.0) Gecko/20120605 Thunderbird/13.0
MIME-Version: 1.0
To: Felix Scheicher <mandavi@web.de>
CC: pgsql-performance@postgresql.org
Subject: Re: queries are fast after dump->restore but slow again
	after some days dispite vacuum
References: <loom.20120719T133136-384@post.gmane.org>
	<5007FFDD.5050103@dunslane.net>
	<loom.20120719T160306-28@post.gmane.org>
In-Reply-To: <loom.20120719T160306-28@post.gmane.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.6 (--)
X-Archive-Number: 201207/235
X-Sequence-Number: 47416


On 07/19/2012 11:13 AM, Felix Scheicher wrote:
> Andrew Dunstan <andrew <at> dunslane.net> writes:
>
>> 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.
>
> That worked like a charm! Many thanks. But how comes, the queries are also fast
> after a restore without the cluster?
>



There is probably a lot of unused space in your table. CLUSTER rewrites 
a fresh copy, as do restore and pg_reorg.

You might also want to try changing the settings on the table so it gets 
much more aggressively auto-vacuumed, so that dead space is made 
available much more quickly, and the table has less chance to get bloated.

cheers

andrew