Re: Reporting planning time with EXPLAIN

On Wed, Dec 28, 2016 at 10:30 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
>> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
>>> I think it's an awful choice of name; it has nothing to do with either
>>> the functionality or the printed name of the field.
>
>> As an example, we might some day wish to include a summary of buffer
>> information at the bottom too when 'buffers' is used. The proposed
>> 'summary' option would cover that nicely, but 'timing' wouldn't. That's
>> actually why I was thinking summary might be a good option to have.
>
> What, would this option then turn off the total-time displays by default?
> I do not see that being a reasonable thing to do. Basically, you're
> taking what seems like a very general-purpose option name and nailing
> it down to mean "print planning time". You aren't going to be able
> to change that later.

I don't intend to use "summary" to print only planning time. As
Stephen has pointed out in his mail, it can be expanded later to
include other things. But I guess, the documentation changes I
included in the patch are the reason behind your objection.

<varlistentry>
+ <term><literal>SUMMARY</literal></term>
+ <listitem>
+ <para>
+ Include planning time, except when used with <command>EXECUTE</command>.
+ Since <command>EXPLAIN EXECUTE</command> displays plan for a prepared
+ query, i.e. a query whose plan is already created, the planning time is
+ not available when <command>EXPLAIN EXECUTE</command> is executed.
+ It defaults to <literal>FALSE</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+

I think I did a bad job there. Sorry for that. I think we should
reword the paragraph as

"Include summary of planning and execution of query. When used
without <literal>ANALYZE</literal> it prints planning time. When used
with <literal>ANALYZE</literal>, this option is considered to be
<literal>TRUE</literal> overriding user specified value and prints
execution time. Since <command>EXPLAIN EXECUTE</command> displays plan
for a prepared query, i.e. a query whose plan is already created, the
planning time is
not available when <command>EXPLAIN EXECUTE</command> is executed. It
defaults to <literal>FALSE</literal>."

We can add more things to this later.

I am not very happy with the sentence explaining ANALYZE, but that's
how it is today. We can change that. With ANALYZE, SUMMARY is ON if
user doesn't specify SUMMARY. But in case user specifies SUMMARY OFF
with ANALYZE, we won't print execution and planning time. It's a
conscious decision by user not to print those things. That will make
the documentation straight forward.

I am not so happy with EXPLAIN EXECUTE either, but it would be better
to clarify the situation. Or we can print planning time as 0 for
EXPLAIN EXECUTE. We can do better there as well. We can print planning
time if the cached plan was invalidated and required planning,
otherwise print 0. That would be a helpful diagnostic.

I do think that there is some merit in reporting planning time as a
whole just like execution time. Planning time is usually so small that
users don't care about how it's split across various phases of
planning. But with more and more complex queries and more and more
planning techniques, it becomes essential to know module-wise (join
planner, subquery planner, upper planner) timings. Developers
certainly would like that, but advanced users who try to control
optimizer might find it helpful. In that case, total planning time
becomes a "summary". In this case "TIMING" would control reporting
granular planning time and SUMMARY would control reporting overall
printing time. I don't intend to add granular timings right now, and
that wasn't something I was thinking of while writing this patch.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company