Re: Vacuuming static tables.

On 5/10/06 1:54 PM, "Daniel T. Staal" <DStaal(at)usa(dot)net> wrote:

> 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.)

My guess is that you are going to have to send some explain analyze output
for the minimum time case and then for something like the "maximum time"
case so that folks can see what is going on.

Sean