Re: Vacuuming static tables.

On May 10, 2006 08:48 am, "Daniel T. Staal" <DStaal@usa.net> wrote:
> However, there is one query that gets run interactively that slows down
> considerably over the course of a week: One memorable week it was taking
> over 15 seconds to run.  Just after a vacuum it takes around 0.7 seconds.

Vacuuming has the side effect of loading the table and indexes into your OS
cache (assuming you have enough RAM).  The subsequent query then doesn't
have to go to disk for the contents.  15 seconds -> .7 seconds sounds very
much like a disk vs. cache speed difference.

Unless you notice the query plan changing in EXPLAIN ANALYZE after running a
vacuum analyze, I think this is the most likely explanation; your query
speed varies depending how much of the data is already in cache.

--
No long, complicated contracts. No actuarial tables to pore over.  Social
Security operates on a very simple principle: the politicians take your
money from you and squander it "