Re: Possible explanations for catastrophic performace deterioration?

Carlos Moreno wrote:

>> , 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??

Because on the older server it is bloated, while on the new one it is
fresh thus no dead tuples.

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

Bloat can explain this as well.

>> 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?

Never. What you need to do is make sure your FSM settings
(fsm_max_pages in particular) are high enough, and that you VACUUM (not
full) frequently enough.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.