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.
BTW
-> Index Only Scan Backward using ix_geo_time_end_user on geo mg (cost=0.00..7369.78 rows=24149 width=8) (actual time=0.020..0.020 rows=0 loops=1)
one of the reasons the query is too expensive... the index has 6GB.
