Re: Vacuuming static tables. - Mailing list pgsql-novice

From Alan Hodgson
Subject Re: Vacuuming static tables.
Date
Msg-id 200605100950.33187@hal.medialogik.com
Whole thread Raw
In response to Vacuuming static tables.  ("Daniel T. Staal" <DStaal@usa.net>)
Responses Re: Vacuuming static tables.
List pgsql-novice
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 "


pgsql-novice by date:

Previous
From: Sean Davis
Date:
Subject: Re: error handling
Next
From: "Daniel T. Staal"
Date:
Subject: Re: Vacuuming static tables.