Re: RFC: Logging plan of the running query

On 2023-12-07 08:33, Rafael Thofehrn Castro wrote:
> Hello hackers,
>
> Last Saturday I submitted a patch to the pgsql-hackers list with the
> title
> "Proposal: In-flight explain logging" with a patch proposing a feature
> very
> similar to the one being worked on in this thread. I should have done
> a better
> search in the commitfest before implementing something from scratch.
>
> So, as recommended by Ashutosh, I am sending an incremental patch
> containing
> an additional feature I personally think we should include: logging
> the plan
> with instrumentation details if enabled.

Thanks for the proposal and making the patch!

> When targeting a query with instrumentation PG should log the complete
> EXPLAIN ANALYZE plan with current row count and, if enabled, timing
> for each
> node. This gives the user not only the ability to see what the plan is
> but also what was executed so far, which is super useful when
> troubleshooting queries that never finish.

I think showing the progress of the query execution would be useful.

OTOH it seems to at least need some modifications around Instrumentation
as your patch.
As a first step, I think it would better to minimize the scope and focus
on the fundamental function.
For the same reason, getting queries for parallel workers is also
prohibited in the current patch as discussed here[1].

[1]
https://www.postgresql.org/message-id/c25ae6015be96a1964eddd964657660b%40oss.nttdata.com

So I think below steps would be better than pushing all the
functionalities to the 1st commit.

- First, develop function to enable output of query
progress(v34-0001-Add-function-to-log-the-plan-of-the-query.patch).
- Then enhance the function
- showing the progress of the query
execution(v34-0002-Log-plan-along-with-instrumentation-details.patch),
etc.

> --https://www.postgresql.org/message-id/CAG0ozMp3g3drnkDa6RZxXO_OmnisL2sD9vBrmpu5fOBoYpC-3w%40mail.gmail.com
> - ExplainState customization
>
> A ExplainState is allocated and customized for the in-flight logging.
> Instrumentation related settings are enabled based on how the target
> query started, which is usually via EXPLAIN ANALYZE or with
> auto_explain.

Does this mean the progress can be got only when the target query was
run with EXPLAIN ANALYZE or auto_explain.log_analyze?

If so, there might be limited situations we can get the progress since I
imagine EXPLAIN ANALYZE is used when user want to get the plan from the
beginning and auto_explain.log_analyze can give negative impact on
performance as described in the manual and there may not be many
environments which enable it.

--
Regards,

--
Atsushi Torikoshi
NTT DATA Group Corporation