Query Performance Test

dcrespo wrote:

I have a query that I want to test twice to see its performance with
and without another program running (anti-virus program). I know that
if you run the same query for the second time, it will be faster than
the first time. So, how can I do to execute it as if it was the first
time again?

In the first place, you cannot make a valid trend line with one data point.

Don't compare the first run. Compare the nth runs in both cases, where n >
some positive integer.

To get statistical confidence, you should measure k runs starting at run n.

Example: Profile runs [4, 13]. n = 4, k = 10.

(Statistics trick: acquire (k+2) samples for each scenario, throw away the
largest and smallest measurements, analyze the remaining k values.)

PG has optimized the query in both scenarios, so you are rendering the PG
aspect constant. Report both the mean and the standard deviation of your k
runs. Compare with and without anti-virus running.

How much control do you have over the test machine's operating profile, i.e.,
what services and background tasks are running, network I/O, other factors
that can influence timing?

--
Lew

So, how can I do to execute it as if it was the first
time again?

Reboot.

As Lew pointed out, that might not actually be a good idea, because caching
means that most queries will most of the time not run with that "first time"
performance.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice