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
