Re: query speed depends on lifetime of frozen db?

On 27 Sep 2002 at 10:58, Andriy Tkachuk wrote:

> Hi hackers.
>
> There is ineresting behavior of some select query mentioned in $subj.
>
> In working db this query takes:
> real 3m10.219s
> user 0m0.074s
> sys 0m0.074s
>
> it's interesting that vacuum or analyze or reinex not helpfull, BUT
>
> if dump this db and create it again (whith another name maybe, no matter,
> just for testing) and query the same query on this db, it takes:
> real 0m6.225s
> user 0m0.072s
> sys 0m0.074s
> (other databases continue running)

Looks like a database defrag to me...

> There is no end of this story!
> With some time (couple of days for example) this the same query overloads
> machine on this new test db also! No one working with this db during this
> time. Works continued only with real working databases. Vacuuming was
> as usual (every 2 hours without -f and with it at night one time) :
> as i said this behavior does not depend on any vacuuming.

was that vacuum full or vacuum analyze? Vacuum full should help in this case..

Is it that some tables with few rows gets updated heavily causing lot of dead
tuples? May be 2 hour is bit too long before vacuum should be called. Try
running table speific vacuum more periodically..

HTH...

Bye
Shridhar

--
lawsuit, n.: A machine which you go into as a pig and come out as a sausage. --
Ambrose Bierce