4

I have written a custom PostgreSQL client and I want to add a function that would automatically capture query execution time.

The current executionTime value in Slonik results records the total query execution time, including the roundtrip and parsing. In order to capture the actual query execution time, I plan to prefix every query with EXPLAIN ANALYZE, parse the explanation and capture the results of the query. However, I cannot find a way to run EXPLAIN ANALYZE and capture the actual query result (in addition to explain output).

How to run EXPLAIN ANALYZE and get results of the query?

Improve this question
12
  • You can't - you can either run analyze or get the results.
    – user1822
    Jul 3, 2019 at 16:47
  • There are workarounds, e.g. one could use auto_explain and capture the notice during the execution. I am looking for a solution that ideally does not require to configure the database instance outside of the query/ procedure.
    – Gajus
    Jul 3, 2019 at 17:20
  • auto_explain also runs the query separately from the explain step
    – user1822
    Jul 3, 2019 at 17:26
  • But there is nothing stopping you from getting the explain output. The question asks how to run EXPLAIN ANALYZE and get results of the query, which auto_explain solves.
    – Gajus
    Jul 3, 2019 at 18:49
  • 1
    I wouldn't expect a negative to be documented as such. The note at postgresql.org/docs/current/auto-explain.html about the performance impact of log_analyze certainly implies that every execution is instrumented from the beginning, not gone back to to re-execute only if it passes log_min_duration.
    – jjanes
    Jul 6, 2019 at 12:52

1 Answer 1

Reset to default
1

If you do:

set auto_explain.log_min_duration TO 0;
set auto_explain.log_level TO notice ;
set auto_explain.log_analyze to on;

then the client will receive the execution analysis. You just need to make it capture that message and distinguish them from other messages that might come along on the same channel. If you need an example, 'psql' does capture and display these messages (but doesn't interpret them).

Improve this answer
2
  • What are the defaults for these, if, for example, we realized that our client now just shows query plans whenever we try to run a query? Thanks!
    – Chris
    Dec 20, 2022 at 6:51
  • 1
    @Chris The default for auto_explain.log_level is LOG, which means the plan just gets written to the server's logfile, and not sent to the client. But if this somehow did get changed to NOTICE, then the client should still be getting the query results in addition to the plan, so that is probably not what I causing your problem. I don't know what could cause what you are seeing.
    – jjanes
    Dec 20, 2022 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.