Re: Query results caching?

On 8/22/05 1:59 PM, "Dann Corbit" <DCorbit(at)connx(dot)com> wrote:

>
>
>> -----Original Message-----
>> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
>> owner(at)postgresql(dot)org] On Behalf Of Ben-Nes Yonatan
>> Sent: Monday, August 22, 2005 9:03 AM
>> To: pgsql-general(at)postgresql(dot)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