Message-ID: <4B425555.6020709@alteeve.com>
Date: Mon, 04 Jan 2010 15:53:41 -0500
From: Madison Kelly <linux@alteeve.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Gary Doades <gpd@gpdnet.co.uk>
Cc: pgsql-performance@postgresql.org
Subject: Re: DB is slow until DB is reloaded
References: <4B423D22.7040803@alteeve.com>
	<4B424215.70605@pinpointresearch.com>
	<4B424FF6.40207@alteeve.com> <4B425222.7070905@gpdnet.co.uk>
In-Reply-To: <4B425222.7070905@gpdnet.co.uk>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Gary Doades wrote:
>  From your queries it definitely looks like its your stats that are the 
> problem. When the stats get well out of date the planner is choosing a 
> hash join because it thinks thousands of rows are involved where as only 
> a few are actually involved. Thats why, with better stats, the second 
> query is using a loop join over very few rows and running much quicker.
> 
> Therefore it's ANALYZE you need to run as well as regular VACUUMing. 
> There should be no need to VACUUM FULL at all as long as you VACUUM and 
> ANALYZE regularly. Once a day may be enough, but you don't say how long 
> it takes your database to become "slow".
> 
> You can VACUUM either the whole database (often easiest) or individual 
> tables if you know in more detail what the problem is and that only 
> certain tables need it.
> 
> Setting up autovacuum may well be sufficient.
> 
> Cheers,
> Gary.

That explains things, thank you!

For the record; It was taking a few months for the performance to become 
intolerable. I've added CLUSTER -> ANALYZE -> VACUUM to my nightly 
routine and dropped the VACUUM FULL call. I'll see how this works.

Cheers!

Madi