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?

  • You can't - you can either run analyze or get the results. – a_horse_with_no_name Jul 3 '19 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 '19 at 17:20
  • auto_explain also runs the query separately from the explain step – a_horse_with_no_name Jul 3 '19 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 '19 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 '19 at 12:52

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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