Re: query speed depends on lifetime of frozen db?

On Fri, 27 Sep 2002, Shridhar Daithankar wrote:

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

it was full with analize
That's what i want to say, that this is very strange for me that vacuum
not helpfull in this situation!

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

As I said there was no work with this test database during the time after which
query becomes overloading. There was just work with another databases.

Thanks,
Andriy.