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

From: Steve Atkins <steve(at)blighty(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: How to evaluate "explain analyze" correctly after "explain" for the same statement ?
Date: 2017-02-16 00:25:59
Message-ID: 626A9493-ED0B-43A1-A690-C32C321CA1BA@blighty.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

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

Cheers,
Steve

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Joshua D. Drake 2017-02-16 00:26:26 Re: Can't restart Postgres
Previous Message Andres Freund 2017-02-16 00:15:11 Re: Potential Bug: Frequent Unnecessary Degeneration