pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedsql execution time in pg_stat_statements
7+ messages / 3 participants
[nested] [flat]
* sql execution time in pg_stat_statements
@ 2021-09-09 18:13 Michel SALAIS <msalais@msym.fr>
0 siblings, 1 reply; 7+ messages in thread
From: Michel SALAIS @ 2021-09-09 18:13 UTC (permalink / raw)
To: pgsql-performance
Hi all,
I think that total_time in pg_stat_statements is cpu time + possible waits.
So, can I say that:
Total_sql_time = total_time + blk_read_time + blk_write_time
Documentation is not clear at all on that.
Thanks in advance
Michel SALAIS
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: sql execution time in pg_stat_statements
@ 2021-09-09 18:49 Michel SALAIS <msalais@msym.fr>
parent: Michel SALAIS <msalais@msym.fr>
0 siblings, 1 reply; 7+ messages in thread
From: Michel SALAIS @ 2021-09-09 18:49 UTC (permalink / raw)
To: pgsql-performance
Just to say that for PostgreSQL 13, total_time is replaced by
total_exec_time + total_plan_time
Michel SALAIS
De : Michel SALAIS <msalais@msym.fr>
Envoyé : jeudi 9 septembre 2021 20:14
À : pgsql-performance@postgresql.org
Objet : sql execution time in pg_stat_statements
Hi all,
I think that total_time in pg_stat_statements is cpu time + possible waits.
So, can I say that:
Total_sql_time = total_time + blk_read_time + blk_write_time
Documentation is not clear at all on that.
Thanks in advance
Michel SALAIS
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: sql execution time in pg_stat_statements
@ 2021-09-10 05:18 Julien Rouhaud <rjuju123@gmail.com>
parent: Michel SALAIS <msalais@msym.fr>
0 siblings, 1 reply; 7+ messages in thread
From: Julien Rouhaud @ 2021-09-10 05:18 UTC (permalink / raw)
To: Michel SALAIS <msalais@msym.fr>; +Cc: pgsql-performance
On Fri, Sep 10, 2021 at 2:49 AM Michel SALAIS <msalais@msym.fr> wrote:
>
> I think that total_time in pg_stat_statements is cpu time + possible waits. So, can I say that:
>
> Total_sql_time = total_time + blk_read_time + blk_write_time
>
> Documentation is not clear at all on that.
In version 12 and below, total_time is the elapsed time between the
execution start and stop, so it includes all underlying events. That
includes any IO activity, wait events or nested statements (if
pg_stat_statemetns.track is set to all). This corresponds to the new
total_exec_time field in version 13 and later.
> Just to say that for PostgreSQL 13, total_time is replaced by “total_exec_time + total_plan_time”
Indeed, as this version also tracks planning activity.
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: sql execution time in pg_stat_statements
@ 2021-09-10 17:12 Michel SALAIS <msalais@msym.fr>
parent: Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Michel SALAIS @ 2021-09-10 17:12 UTC (permalink / raw)
To: 'Julien Rouhaud' <rjuju123@gmail.com>; +Cc: pgsql-performance
-----Message d'origine-----
De : Julien Rouhaud <rjuju123@gmail.com>
Envoyé : vendredi 10 septembre 2021 07:18
À : Michel SALAIS <msalais@msym.fr>
Cc : postgres performance list <pgsql-performance@postgresql.org>
Objet : Re: sql execution time in pg_stat_statements
On Fri, Sep 10, 2021 at 2:49 AM Michel SALAIS <msalais@msym.fr> wrote:
>
> I think that total_time in pg_stat_statements is cpu time + possible waits. So, can I say that:
>
> Total_sql_time = total_time + blk_read_time + blk_write_time
>
> Documentation is not clear at all on that.
In version 12 and below, total_time is the elapsed time between the execution start and stop, so it includes all underlying events. That includes any IO activity, wait events or nested statements (if pg_stat_statemetns.track is set to all). This corresponds to the new total_exec_time field in version 13 and later.
> Just to say that for PostgreSQL 13, total_time is replaced by “total_exec_time + total_plan_time”
Indeed, as this version also tracks planning activity.
--------------------------------------------------------------------------------
Hi,
I thaught that total_time (total_exec_time + total_plan_time) included I/O but when we have blk_read_time + blk_write_time equals several times total_time it is difficult to continue to think that...
So What is really total_time (total_exec_time + total_plan_time) ?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: sql execution time in pg_stat_statements
@ 2021-09-10 17:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michel SALAIS <msalais@msym.fr>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2021-09-10 17:42 UTC (permalink / raw)
To: Michel SALAIS <msalais@msym.fr>; +Cc: 'Julien Rouhaud' <rjuju123@gmail.com>; pgsql-performance
"Michel SALAIS" <msalais@msym.fr> writes:
> I thaught that total_time (total_exec_time + total_plan_time) included I/O but when we have blk_read_time + blk_write_time equals several times total_time it is difficult to continue to think that...
That's an interesting report, but on the whole I'd be more inclined
to disbelieve the I/O timings than the overall time. Can you create
a reproducible test case where this happens? Also, exactly what
PG version and pg_stat_statements version are you using?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: sql execution time in pg_stat_statements
@ 2021-09-10 17:43 Julien Rouhaud <rjuju123@gmail.com>
parent: Michel SALAIS <msalais@msym.fr>
1 sibling, 0 replies; 7+ messages in thread
From: Julien Rouhaud @ 2021-09-10 17:43 UTC (permalink / raw)
To: Michel SALAIS <msalais@msym.fr>; +Cc: pgsql-performance
On Sat, Sep 11, 2021 at 1:12 AM Michel SALAIS <msalais@msym.fr> wrote:
>
> I thaught that total_time (total_exec_time + total_plan_time) included I/O but when we have blk_read_time + blk_write_time equals several times total_time it is difficult to continue to think that...
Maybe not that difficult. If the queries use parallelism, the query
total execution time may be say 1 second, but if there were X workers
it could actually cumulate X+1 seconds of execution time, and
therefore reach more than 1 second of cumulated read/write time.
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: sql execution time in pg_stat_statements
@ 2021-09-19 15:41 Michel SALAIS <msalais@msym.fr>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Michel SALAIS @ 2021-09-19 15:41 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: 'Julien Rouhaud' <rjuju123@gmail.com>; pgsql-performance
Hi Tom,
I was out of this list for a while.
Unfortunately, I have now no access to the server where I have seen this.
Best regards
Michel SALAIS
-----Message d'origine-----
De : Tom Lane <tgl@sss.pgh.pa.us>
Envoyé : vendredi 10 septembre 2021 19:42
À : Michel SALAIS <msalais@msym.fr>
Cc : 'Julien Rouhaud' <rjuju123@gmail.com>; 'postgres performance list'
<pgsql-performance@postgresql.org>
Objet : Re: sql execution time in pg_stat_statements
"Michel SALAIS" <msalais@msym.fr> writes:
> I thaught that total_time (total_exec_time + total_plan_time) included I/O
but when we have blk_read_time + blk_write_time equals several times
total_time it is difficult to continue to think that...
That's an interesting report, but on the whole I'd be more inclined to
disbelieve the I/O timings than the overall time. Can you create a
reproducible test case where this happens? Also, exactly what PG version
and pg_stat_statements version are you using?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-09-19 15:41 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-09 18:13 sql execution time in pg_stat_statements Michel SALAIS <msalais@msym.fr>
2021-09-09 18:49 ` Michel SALAIS <msalais@msym.fr>
2021-09-10 05:18 ` Julien Rouhaud <rjuju123@gmail.com>
2021-09-10 17:12 ` Michel SALAIS <msalais@msym.fr>
2021-09-10 17:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-09-19 15:41 ` Michel SALAIS <msalais@msym.fr>
2021-09-10 17:43 ` Julien Rouhaud <rjuju123@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox