From: | Madison Kelly <linux(at)alteeve(dot)com> |
---|---|
To: | Gary Doades <gpd(at)gpdnet(dot)co(dot)uk> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: DB is slow until DB is reloaded |
Date: | 2010-01-04 20:53:41 |
Message-ID: | 4B425555.6020709@alteeve.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Brad Nicholson | 2010-01-04 21:00:56 | Re: DB is slow until DB is reloaded |
Previous Message | Gary Doades | 2010-01-04 20:40:02 | Re: DB is slow until DB is reloaded |