Re: Query results caching? - Mailing list pgsql-general

Sean Davis wrote:
> On 8/22/05 1:59 PM, "Dann Corbit" <DCorbit@connx.com> wrote:
>
>
>>
>>>-----Original Message-----
>>>From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>>>owner@postgresql.org] On Behalf Of Ben-Nes Yonatan
>>>Sent: Monday, August 22, 2005 9:03 AM
>>>To: pgsql-general@postgresql.org
>>>Subject: [GENERAL] Query results caching?
>>>
>>>Hi all,
>>>
>>>I dont know how its called but I noticed that when I query the db for
>>>the first time it give me the result slower then the next times ill
>>>repeat the same exact query, I figure that its some kind of caching so
>>>henceforth the title of the mail :)
>>
>>The operating system and the database will both percolate frequently
>>used information from disk into memory.  Particularly if they are SELECT
>>queries, they will get faster and faster.
>>
>>
>>>Anyway I would want to be able to delete that "caching" after every
>>>query test that I run, cause I want to see the real time results for
>>
>>my
>>
>>>queries (its for a searching option for users so it will vary alot).
>>
>>Those are the real times for your queries.
>>
>>
>>>Is it possible to do it manually each time or maybe only from the
>>>configuration?
>>
>>You will have to query a different table each time.
>
>
> Just to extend this notion a bit, if you want to test your application
> speed, you may want to generate "real-world" input to determine the actual
> behavior/speed under real conditions.  As Dann pointed out, the results for
> timings are "real" in that if the user generated the queries as you did, the
> timing results would be (nearly) the same as for you.  It seems that your
> concern is that the user will not generate the same type of input that you
> did (that it will vary more), so the best solution may be to actually
> generate some test queries that actually conform to what you think the user
> input will look like.
>
> Sean
>
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".

Thanks alot again,
Yonatan