Re: Possible explanations for catastrophic performace deterioration?

Jonah H. Harris wrote:
> You didn't specify the database size

Oops, sorry about that one --- the full backup is a 950MB file. The
entire database
should fit in memory (and the effective_cache_size was set to 2GB for
the machine
with 4GB of memory)

> , but my guess is that the total
> data size about enough to fit in shared_buffers or kernel cache. On
> the new system (or dropped/recreated database), it would've all or
> mostly fit in memory which would make things like count(*) work
> quickly.

I don't understand this argument --- the newer system has actually less
memory
than the old one; how could it fit there and not on the old one? Plus,
how could
dropping-recreating the database on the same machine change the fact
that the
entire dataset entirely fit or not in memory??

The other part that puzzled me is that after running "select count(*)
... " several
times (that particular table is *very* small --- just 200 thousand
records of no
more than 100 or 200 bytes each), then the entire table *should* have been
in memory ... Yet, it would still take a few seconds (notice that
there was a
*considerable* improvement from the first run of that query to the
second one
on the old server --- from more than a minute, to just above two
seconds.... But
still, on the new server, and after recreating the DB on the old one, it
runs in
*no time* the first time).

> My guess is that a vacuum full would've brought the other database
> back up to speed.

I'm furious now that it didn't occur to me the vacuum full until *after*
I had
recreated the database to see th problem disappear...

I wonder if I should then periodically run a vacuum full --- say, once a
week?
Once a month?

> In the future, you probably want to set fillfactor
> to a reasonable amount to account for updates-to-blocks-between-vacuum
> to try and capture as few row-migrations as possible.
>

Could you elaborate a bit on this? Or point me to the right places in the
documentation to help me understand the above?? (I'm 100% blank after
reading the above paragraph)

Thanks,

Carlos
--