Re: RFC: Logging plan of the running query

On Tue, Jul 13, 2021 at 11:11 PM Masahiro Ikeda
<ikedamsh(at)oss(dot)nttdata(dot)com> wrote:

> When I tried this feature, I realized two things. So, I share them.

Thanks for your review!

> (1) About output contents
>
> > The format of the query plan is the same as when <literal>FORMAT
> > TEXT</literal>
> > and <literal>VEBOSE</literal> are used in the
> > <command>EXPLAIN</command> command.
> > For example:

> I think the above needs to add COSTS and SETTINGS options too, and it's
> better to use an
> example which the SETTINGS option works like the following.

Agreed. Updated the patch.

> (2) About EXPLAIN "BUFFER" option
>
> When I checked EXPLAIN option, I found there is another option "BUFFER"
> which can be
> used without the "ANALYZE" option.
>
> I'm not sure it's useful because your target use-case is analyzing a
> long-running query,
> not its planning phase. If so, the planning buffer usage is not so much
> useful. But, since
> the overhead to output buffer usages is not high and it's used for
> debugging use cases,
> I wonder it's not a bad idea to output buffer usages too. Thought?

As you pointed out, I also think it would be useful when queries are
taking a long time in the planning phase.
However, as far as I read ExplainOneQuery(), the buffer usages in the
planner phase are not retrieved by default. They are retrieved only when
BUFFERS is specified in the EXPLAIN.

If we change it to always get the buffer usages and expose them as a
global variable, we can get them through pg_log_current_plan(), but I
think it doesn't pay.

Regards,

--
Atsushi Torikoshi
NTT DATA CORPORATION