Re: query speed depends on lifetime of frozen db?

On Fri, 27 Sep 2002, Andriy Tkachuk wrote:

> On Fri, 27 Sep 2002, Martijn van Oosterhout wrote:
>
> > On Fri, Sep 27, 2002 at 12:50:14PM +0300, Andriy Tkachuk wrote:
> > > On Fri, 27 Sep 2002, Martijn van Oosterhout wrote:
> > >
> > > > On Fri, Sep 27, 2002 at 11:49:08AM +0300, Andriy Tkachuk wrote:
> > > > > On Fri, 27 Sep 2002, Shridhar Daithankar wrote:
> > > > > > 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!
> > > >
> > > > Ok, can you post the result of VACUUM FULL VERBOSE ANALYSE ?
> >
> > <snip>
> >
> > Um, from the looks of that output, it seems your entire DB is less than 2MB,
> > right? So it should be totally cached. So it must be your query at fault.
> > What is the output of EXPLAIN ANALYSE <query>;
>
> db ~ 10M, but i like your guess.
>
> my OS:
> Linux 2.4.9-13custom #1 Fri Feb 15 20:03:52 EST 2002 i686

and 256M phys_mem and
shared_buffers = 1024
...

i test it on linux and FreeBSD (with kern.ipc.shm_use_phys=1, ~400M phys_mem,
and soft-updates on UFS) and there is the same behavior so i think that
this problem is not OS specific.

Also just after dumping and restoring the test db i restart pg and
the query was fast though, so i think that it's not caching.

It seems like there are some relations between databases that speed
of this query depends on with time.

Thanks,
Andriy.