Sean Davis wrote:
On 8/22/05 1:59 PM, "Dann Corbit" <DCorbit@xxxxxxxxx> wrote:
-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-
owner@xxxxxxxxxxxxxx] On Behalf Of Ben-Nes Yonatan
Sent: Monday, August 22, 2005 9:03 AM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
