Re: Planning time in explain/explain analyze

On Mon, Jan 13, 2014 at 3:40 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Jan 13, 2014 at 3:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Meh. Why? This would only come into play for EXPLAIN EXECUTE stmtname.
>>> I don't think users would be surprised to see a report of minimal planning
>>> time for that. In fact, it might be a good thing, as it would make it
>>> easier to tell the difference between whether you were seeing a generic
>>> plan or a custom plan for the prepared statement.
>
>> It would also make it easier to be wrong. If you want to display that
>> information explicitly, fine. But asking the user to use the elapsed
>> time to guess whether or not we really planned anything is just going
>> to confuse people who don't have enough experience with the system to
>> know what the boundary is between the largest time that could be a
>> cache lookup and the smallest time that could be real planning
>> activity. And that means virtually everyone, me included.
>
> If you're saying that you'd like EXPLAIN to explicitly mention whether
> the plan was cached or custom, I don't have any great complaint about
> that. I'm just not seeing how you arrive at the conclusion that we
> mustn't report the amount of time EXPLAIN spent to get the plan.
> If we do what you're proposing we'll just have a different set of confused
> users, who will be wondering how EXPLAIN could have managed to spend
> 100 msec planning something when the EXPLAIN only took 10 msec in toto
> according to psql.

What I'm saying is that if EXPLAIN reports something that's labelled
"Planning Time", it should *be* the planning time, and not anything
else. When we retrieve a plan from cache, it would be sensible not to
report the planning time at all, and IMHO it would also be sensible to
report the time it actually took to plan whenever we originally did
it. But reporting a value that is not the planning time and calling
it the planning time does not seem like a good idea to me.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company