From: | "Daniel T(dot) Staal" <DStaal(at)usa(dot)net> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Vacuuming static tables. |
Date: | 2006-05-10 17:54:17 |
Message-ID: | 49684.63.172.115.138.1147283657.squirrel@MageHandbook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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.
---------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Alan Hodgson | 2006-05-10 17:59:34 | Re: Vacuuming static tables. |
Previous Message | Alan Hodgson | 2006-05-10 16:50:33 | Re: Vacuuming static tables. |