public inbox for [email protected]  

help / color / mirror / Atom feed
From: torikoshia <[email protected]>
To: Rafael Thofehrn Castro <[email protected]>
Cc: Étienne BERSAC <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: Re: RFC: Logging plan of the running query
Date: Mon, 11 Dec 2023 14:46:23 +0900
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAG0ozMqmjpUG65sDQ+RA41mhr_1+Wf2Kk88V4_3MDCy9Xk4-+w@mail.gmail.com>
References: <[email protected]>
	<CAAaqYe9TPyWe9yoOm5Jz_39gSnhCAYXozc698jkyegM1x74Qjg@mail.gmail.com>
	<CAAaqYe9Mse7JC78DgT97sXoB6U+E5OL3zJAYRSsgy-TPP9PXmw@mail.gmail.com>
	<[email protected]>
	<CAAaqYe_5p_MkBCgSYX=S7G3ZKi=2=_6bZQFzTgVYyv=1NRikpw@mail.gmail.com>
	<[email protected]>
	<CAAaqYe_hyWLs-ObFQfu54yA-ZOjNCd-L_Z=1TnYjYNqrLuGiXQ@mail.gmail.com>
	<[email protected]>
	<CAAaqYe_p-849MtsVgN9_y-YL-uFzLFMpGw38T3Omb11gw-=K1w@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAExHW5ujL=mzbjxf6xtyU-Ft6LMBMEmt8H4ZSDiiZre0Fef0jg@mail.gmail.com>
	<[email protected]>
	<CAExHW5s+T0Ds-h85y92MsryLqup8fuAT8bovgGGaNnLt=OrQOg@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAExHW5vnoyd750bpjOJr58F33tN+hROAqVqv1nYRVPwd9XtgSg@mail.gmail.com>
	<CAExHW5t+xh95_LZjkUZARDbV=gS5pBJghG8x=Xnjb2psWr0R6g@mail.gmail.com>
	<[email protected]>
	<CAExHW5utSXjEMh6BWBPEe3Ks70T12VcGZqR8YH1owhZoROv4fw@mail.gmail.com>
	<[email protected]>
	<CAExHW5sh4ahrJgmMAGfptWVmESt1JLKCNm148XVxTunRr+-6gA@mail.gmail.com>
	<CAG0ozMqmjpUG65sDQ+RA41mhr_1+Wf2Kk88V4_3MDCy9Xk4-+w@mail.gmail.com>

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.gma...
> - 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






view thread (30+ messages)  latest in thread

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: RFC: Logging plan of the running query
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox