Re: How to evaluate "explain analyze" correctly after "explain" for the same statement ?

Patrick B <patrickbakerbr(at)gmail(dot)com> writes:
> For the first time I ran the query, it took >10 seconds. Now it is taking
> less than a second.
> How can I clear for good the cache? So i can have a real idea of how long
> the query takes to run?

TBH, I think you're probably obsessing over the wrong thing. It's
highly unlikely that cache effects would be sufficient to explain
a 10-second runtime for a query that otherwise takes less than 1 msec.
What seems more likely is that the query was waiting on a lock, or
something else that created a non-cache-related bottleneck.

Also, I think you're coming at things from completely the wrong direction
if you believe that the worst-case, nothing-in-any-level-of-cache case
is the "true" runtime. Most people who are worried about performance
spend a great deal of effort ensuring that that case doesn't happen to
them in practice. As an example, the first few queries in a fresh
session will almost always run slower than later queries, because it
takes some time to ramp up the new backend's local catalog caches to have
all the useful data in them. But the correct response to that observation
is to try to make sure your sessions last awhile and execute many queries,
not to decide that the uncached state is the "true" runtime. It's only
representative if you're intentionally shooting yourself in the foot.

regards, tom lane