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

2017-02-16 14:08 GMT+13:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> 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
>

I've got two different scenarios:

Production database server > PG 9.2

- I ran one single time, in a slave server that no queries go to that
server, and it took >10 seconds.

Test database server > PG 9.2

- This is the server that I'm working on. When I ran the query here for
the first time, it also took >10 seconds. And it is not a LOCK as no one
was/is using this database server. (using explain analyze)
- When I ran the query for the second time (using explain analyze), it
took 1 second to run.
- On the third time, it took < than 1 second.
- This server I can reboot the machine/PG or stop/start Postgres Process.
- I've already done: service postgresql stop;
sync; echo 3 > /proc/sys/vm/drop_caches; service postgresql start

I've made some changes to the query and would like to get its real runtime
so I can compare and keep working if I need to.

*The question is:*

How can I clear the cache, to get a real good estimation of how much the
query is taking to run?

P.