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

2017-02-16 14:57 GMT+13:00 Patrick B <patrickbakerbr(at)gmail(dot)com>:
>
> 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.
>

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.