Horton

Re: RFC: Logging plan of the running query

torikoshia <torikoshia@oss.nttdata.com>

View thread
From: torikoshia <torikoshia@oss.nttdata.com>
To: Rafael Thofehrn Castro <rafaelthca@gmail.com>
Cc: Étienne BERSAC <etienne.bersac@dalibo.com>, Andres Freund <andres@anarazel.de>, jtc331@gmail.com, pgsql-hackers@lists.postgresql.org, ashutosh.bapat.oss@gmail.com
Date: 2023-12-11T05:46:23Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. ecpg: Catch zero-length Unicode identifiers correctly

  2. Improve warning message in pg_signal_backend()

  3. Add assert to ensure that page locks don't participate in deadlock cycle.

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