Обсуждение: Question on what Duration in the log

"Vitale, Anthony, Sony Music" <anthony.vitale@sonymusic.com> writes:
> In my PG Version 14 log I have set to log the duration of sql (Example of one log row below).
> 2025-04-08 07:31:57 UTC:XXX(55994):XXX:[8949]:LOG:  duration: 6555.756 ms  execute S_381: {Actual Parameterized Sql
executed}
> This Shows the Execution of a JDBC Server Side Prepared statement (i.e S_381) which took 6.5 seconds.

> When I run the Sql using explain analyze it runs shows an execution time of 73 ms
> Planning Time: 0.773 ms
> Execution Time: 73.578 ms

They're not measuring the same thing really, because EXPLAIN isn't
counting the time needed to transmit data to the client.

This is not just a matter of network overhead (though that can be
substantial) but also the time required to format the values into
text.  If you are talking about large values, which I bet you are,
there is also the time needed to fetch them from out-of-line TOAST
storage, which is another thing that doesn't happen in EXPLAIN.

Since v17, EXPLAIN has grown a SERIALIZE option that causes it to
do everything except the network transmission, which might be
of interest in analyzing this further.

            regards, tom lane

Thanks Tom

Perfect.

Anthony Vitale
 
This message is only for the use of the persons(s) to whom it is intended. It may contain privileged and confidential
informationwithin the meaning of applicable law. If you are not the intended recipient, please do not use this
informationfor any purpose, destroy this message and inform the sender immediately. The views expressed in this
communicationmay not necessarily be the views held by Sony Music Entertainment 

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Wednesday, April 9, 2025 11:02 AM
To: Vitale, Anthony, Sony Music <anthony.vitale@sonymusic.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Question on what Duration in the log

EXTERNAL SENDER


"Vitale, Anthony, Sony Music" <anthony.vitale@sonymusic.com> writes:
> In my PG Version 14 log I have set to log the duration of sql (Example of one log row below).
> 2025-04-08 07:31:57 UTC:XXX(55994):XXX:[8949]:LOG:  duration: 6555.756
> ms  execute S_381: {Actual Parameterized Sql executed} This Shows the Execution of a JDBC Server Side Prepared
statement(i.e S_381) which took 6.5 seconds. 

> When I run the Sql using explain analyze it runs shows an execution
> time of 73 ms Planning Time: 0.773 ms Execution Time: 73.578 ms

They're not measuring the same thing really, because EXPLAIN isn't counting the time needed to transmit data to the
client.

This is not just a matter of network overhead (though that can be
substantial) but also the time required to format the values into text.  If you are talking about large values, which I
betyou are, there is also the time needed to fetch them from out-of-line TOAST storage, which is another thing that
doesn'thappen in EXPLAIN. 

Since v17, EXPLAIN has grown a SERIALIZE option that causes it to do everything except the network transmission, which
mightbe of interest in analyzing this further. 

                        regards, tom lane


This email originated from outside of Sony Music.  Do not click links or open attachments unless you recognize the
senderand know the content is safe.