On Mon, 2010-01-04 at 15:53 -0500, Madison Kelly wrote: > 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. I think you are going down the wrong route here - you should be looking at preventative maintenance instead of fixing it after its broken. Ensure that autovacuum is running for the database (assuming that you are on a relatively modern version of PG), and possibly tune it to be more aggressive (we can help). This will ensure that the condition never comes up. ps - if you do go with the route specify, no need to VACUUM after the CLUSTER. CLUSTER gets rid of the dead tuples - nothing for VACUUM to do. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
