Re: Query results caching?

On Mon, Aug 22, 2005 at 10:13:49PM +0200, Ben-Nes Yonatan wrote:
> I think that I was misunderstood, Ill make an example:
> Lets say that im making the following query for the first time on the
> "motorcycles" table which got an index on the "manufacturer" field:
>
> EXPLAIN ANALYZE SELECT manufacturer FROM motorcycles WHERE
> manufacturer='suzuki';
> ... Total runtime: 3139.587 ms
>
> Now im doing the same query again and i get a much faster result (cause
> of the "caching"): Total runtime: 332.53 ms
>
> After both of those queries I drop the index and query the table again
> with the exact same query as before and now I receive: Total runtime:
> 216834.871 ms
>
> And for my last check I run the exact same query again (without creating
> the INDEX back again) and I get quite similar result to my third query:
> Total runtime: 209218.01 ms
>
>
> My problem is that (maybe I just dont understand something basic
> here...) the last 2 (also the second query but I dont care about that)
> queries were using the "cache" that was created after the first query
> (which had an INDEX) so none of them actually showed me what will happen
> if a client will do such a search (without an INDEX) for the first time.
>
> I want to delete that "caching" after I do the first 2 queries so my
> next queries will show me "real life results".

Emptying the cache will not show real-life results. You are always going
to have some stuff cached, even if you get a query for something new. In
this case (since you'll obviously want those indexes there), after some
amount of time you will have most (if not all) of the non-leaf index
pages cached, since they take a fairly small amount of memory and are
frequently accessed. This makes index traversal *much* faster than your
initial case shows, even if you query on something different each time.
Testing with a completely empty cache just isn't that realistic.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com 512-569-9461