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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
