Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id D459C632A55
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  4 Jan 2010 16:30:58 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 96562-04
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Mon,  4 Jan 2010 20:30:44 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from smtp.iplink.net (smtp.iplink.net [192.139.81.186])
	by mail.postgresql.org (Postfix) with ESMTP id 3D18D6324E5
	for <pgsql-performance@postgresql.org>;
	Mon,  4 Jan 2010 16:30:48 -0400 (AST)
Received: from [10.255.0.247] (fw.iplink.net [192.139.81.98])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by smtp.iplink.net (Postfix) with ESMTP id CB020A6753A;
	Mon,  4 Jan 2010 15:30:46 -0500 (EST)
Message-ID: <4B424FF6.40207@alteeve.com>
Date: Mon, 04 Jan 2010 15:30:46 -0500
From: Madison Kelly <linux@alteeve.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Steve Crawford <scrawford@pinpointresearch.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: DB is slow until DB is reloaded
References: <4B423D22.7040803@alteeve.com>
	<4B424215.70605@pinpointresearch.com>
In-Reply-To: <4B424215.70605@pinpointresearch.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.722 tagged_above=-10 required=5 tests=AWL=-1.726,
	BAYES_20=-0.74, FH_DATE_PAST_20XX=3.188
X-Spam-Level: 
X-Archive-Number: 201001/10
X-Sequence-Number: 36890

Steve Crawford wrote:
> Madison Kelly wrote:
>> Hi all,
>>
>>   I've got a fairly small DB...
>>
>>   It slows down over time and I can't seem to find a way to get the
>> performance to return without doing a dump and reload of the database...
> 
> Some questions:
> 
> Is autovacuum running? This is the most likely suspect. If not, things 
> will bloat and you won't be getting appropriate "analyze" runs. Speaking 
> of which, what happens if you just run "analyze"?
> 
> And as long as you are dumping and reloading anyway, how about version 
> upgrading for bug reduction, performance improvement, and cool new 
> features.
> 
> Cheers,
> Steve
> 

Yup, I even tried manually running 'VACUUM FULL' and it didn't help. As 
for upgrading;

a) I am trying to find a way around the dump/reload. I am doing it as a 
"last resort" only.
b) I want to keep the version in CentOS' repo.

I'd not tried simply updating the stats via ANALYZE... I'll keep an eye 
on performance and if it starts to slip again, I will run ANALYZE and 
see if that helps. If there is a way to run ANALYZE against a query that 
I am missing, please let me know.

Madi