Re: Reporting planning time with EXPLAIN

On Tue, Mar 7, 2017 at 9:23 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> Ashutosh,
>
> * Ashutosh Bapat (ashutosh(dot)bapat(at)enterprisedb(dot)com) wrote:
>> Here are patches for follwing
>> 1. pg_explain_plan_time_v3 adds SUMMARY option which behaves as:
>> SUMMARY when ON prints planning time. With ANALYZE ON, it also prints
>> execution time. When user explicitly uses SUMMARY OFF, it does not
>> print planning and execution time (even when ANALYZE is ON). By
>> default SUMMARY is ON when ANALYZE is ON, otherwise SUMMARY defaults
>> to OFF. Attached explain_summary_examples.out.txt shows examples.
>>
>> 2. explain_exec_timing adds support to print planning time in EXPLAIN
>> EXECUTE output with SUMMARY option. In this case, planning time
>> includes time required to fetch the plan from cache and plan the query
>> if necessary (i.e. after invalidation or the first time it's
>> executed.) E.g.
>
> I'm going through these with an eye towards committing them soon. I've
> already adjusted some of the documentation and comments per our earlier
> discussion

Thanks a lot.

> but I'm now reviewing the changes to ExplainExecuteQuery()
> and trying to understand the reasoning for not including the
> EvaluateParams() call in the planning time. Not including that feels to
> me like we're ending up leaving something out of the overall timing
> picture, which doesn't seem right.
>
> If we do include that, then planning time+execution time will equal the
> overall query time and that feels like the right approach to use here.
> Otherwise the overall query time is "planning time+execution
> time+something else that we don't tell you about" which doesn't seem
> good to me.

Thanks for pointing that out. I didn't include parameter evaluation
time earlier, since it's not strictly planning time. But I think it's
important to include the parameter evaluation since different set of
parameters may cause planner to create a customized plan. So it looks
like something we should include in the planning time. I have updated
the patch to do so. I have also rebased the patches on top of current
head, resolving a conflict. The new patches have slightly different
names than previous ones, since I am now using git format-patch to
create those.

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