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

2017-02-16 13:25 GMT+13:00 Steve Atkins <steve(at)blighty(dot)com>:

>
> > On Feb 15, 2017, at 3:58 PM, Patrick B <patrickbakerbr(at)gmail(dot)com> wrote:
> >
> > Hi all,
> >
> > I just got a quick question about warm-cache. I'm using PG 9.2.
> >
> > When I execute this statement soon after I start/restart the database:
> >
> > explain select id from test where id = 124;
> >
> > The runtime is 40ms.
> >
> > Then, If I execute this statement just after the above one;
> >
> > explain analyze select id from test where id = 124;
> >
> > The runtime is 0.8ms.
>
> This doesn't make seem to make sense.
>
> "explain select ..." doesn't run the query. All it shows is the plan the
> planner chose and some estimates of the "cost" of different steps, with no
> time. Where are you getting 40ms from in this case?
>

From my DB-VISUALIZER - it shows how long the query took.

>
> "explain analyze select ..." does run the query, along with some -
> potentially non-trivial - instrumentation to measure each step of the plan,
> so you can see whether the planner estimates are reasonable or wildly off.
>
>
> Well.. that is what's happening.

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?