2017-02-16 14:08 GMT+13:00 Tom Lane <t...@sss.pgh.pa.us>: > Patrick B <patrickbake...@gmail.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.
