MIME-Version: 1.0
From: Prasanna Thirugnanasambandam <balajipts07@gmail.com>
Date: Thu, 13 Mar 2025 15:21:27 +0530
Message-ID: <CAPhJzzWnGBbyOW4a5acgBqCgQef5Je-JSrjXBAnE86BukWHw+w@mail.gmail.com>
Subject: Finding execution time for a query
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000aebf6c0630364498"
Archived-At: <https://www.postgresql.org/message-id/CAPhJzzWnGBbyOW4a5acgBqCgQef5Je-JSrjXBAnE86BukWHw%2Bw%40mail.gmail.com>
Precedence: bulk

--000000000000aebf6c0630364498
Content-Type: text/plain; charset="UTF-8"

Hello all,

How do I find the execution time of a query?
I tried these methods:
I used

pgbench - pgbench -n -t 1 -f ./query.sql <database name>

and got:

    latency average = 9.787 ms <- should i take this as the execution time?
    initial connection time = 12.481 ms
    tps = 102.176356 (without initial connection time)

used *pg_stat_statements* to fetch execution time for that query.

SELECT total_exec_time FROM pg_stat_statements where query = '<query here>'

got:

      total_exec_time
    --------------------
     12.242579000000001
    (1 row)

used *EXPLAIN ANALYZE*, got:

                                           QUERY PLAN

------------------------------------------------------------------------------------------------
 <query description>  (cost=0.00..0.01 rows=0 width=0) (actual
time=0.182..0.182 rows=0 loops=1)
   ->  Result  (cost=0.00..0.01 rows=1 width=230) (actual time=0.034..0.034
rows=1 loops=1)
 Planning Time: 0.021 ms
 Execution Time: 0.195 ms
(4 rows)


Now, which among these gives me the actual execution time of the query? Or
rather, how can i find out the actual execution time of the query?

--000000000000aebf6c0630364498
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr">Hello all,<br><br>How do I find the execu=
tion time of a query?<br>I tried these methods:<br><div style=3D"text-align=
:left">I used</div><blockquote style=3D"text-align:left">pgbench - pgbench =
-n -t 1 -f ./query.sql &lt;database name&gt;</blockquote></div><div dir=3D"=
ltr">and got:<br><blockquote>=C2=A0 =C2=A0 latency average =3D 9.787 ms &lt=
;- should i take this as the execution time?<br>=C2=A0 =C2=A0 initial conne=
ction time =3D 12.481 ms<br>=C2=A0 =C2=A0 tps =3D 102.176356 (without initi=
al connection time)</blockquote>used <i>pg_stat_statements</i> to fetch exe=
cution time for that query.<br></div><blockquote dir=3D"ltr">SELECT total_e=
xec_time FROM pg_stat_statements where query =3D &#39;&lt;query here&gt;=
9;</blockquote><div dir=3D"ltr">got:<br><blockquote>=C2=A0 =C2=A0 =C2=A0 to=
tal_exec_time =C2=A0 <br>=C2=A0 =C2=A0 --------------------<br>=C2=A0 =C2=
=A0 =C2=A012.242579000000001<br>=C2=A0 =C2=A0 (1 row)</blockquote>used <i>E=
XPLAIN ANALYZE</i>, got:<br><blockquote>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0QUERY PLAN =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 <br>---------------=
---------------------------------------------------------------------------=
------<br>=C2=A0&lt;query description&gt; =C2=A0(cost=3D0.00..0.01 rows=3D0=
 width=3D0) (actual time=3D0.182..0.182 rows=3D0 loops=3D1)<br>=C2=A0 =C2=
=A0-&gt; =C2=A0Result =C2=A0(cost=3D0.00..0.01 rows=3D1 width=3D230) (actua=
l time=3D0.034..0.034 rows=3D1 loops=3D1)<br>=C2=A0Planning Time: 0.021 ms<=
br>=C2=A0Execution Time: 0.195 ms<br>(4 rows)</blockquote><br>Now,
 which among these gives me the actual execution time of the query? Or=20
rather, how can i find out the actual execution time of the query?</div></d=
iv>

--000000000000aebf6c0630364498--