Re: Vacuuming static tables.

On Wed, May 10, 2006 12:50 pm, Alan Hodgson said:
> On May 10, 2006 08:48 am, "Daniel T. Staal" <DStaal(at)usa(dot)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.

Then the question is why doesn't running it twice in a row speed it up?
(When I plug in *exactly* the same values.)

If I vacuum one day, normally when I come in in the morning it takes about
twice as long as the minimum time. The 15 seconds was the exceptional
value: the one that brought this to my attention. (Because waiting 20+
seconds for a web page that normally takes only one or two is noticable.
;)) That was probably a busy week for that table: we added a big new
customer, and this table tracks customer 'mailboxes'. So there may have
been 10 inserts or so that week.

Still, that's probably a good answer, and at least part of what I am seeing.

(The query plan doesn't change. I had already checked that. The only
thing that changes is the estimated time and costs of each step.)

Daniel T. Staal

---------------------------------------------------------------
This email copyright the author. Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes. This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.
---------------------------------------------------------------