Finding execution time of a psql query (without any connection latency/overhead)

Question

How do I find the execution time of a query?

I tried these methods:

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

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)

Using EXPLAIN ANALYZE:

                                           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 of those gives me the actual execution time of the query? Or rather, how can I find out the actual execution time?

Laurenz Albe · Answer 1 · 2025-03-13T11:04:52.923

The three attempts you made measure different things:

The 9.787 ms measured by pgbench is the average duration of a statement, measured on the client side. It includes the network latency.
The total_exec_time from pg_stat_statements is the sum of all execution times of that statement, measured on the server side. It does not include the network latency. In addition, pg_stat_statement aggregates statements that only differ in constants.
EXPLAIN (ANALYZE) also measures the execution time on the server side. However, the instrumentation that allows EXPLAIN to measure all aspects of the statement execution incurs a substantial overhead compared to the regular execution of the statement.

In addition to that, the execution time for a statement can vary from time to time because of caching issues and general concurrency randomness. Repeat tests a couple of times to gain confidence in the results.

One thing that I use to determine the execution time of a statement is the following:

connect with psql and run the meta-command \timing to measure the execution time on the client side
run SELECT 1 a couple of times and take the average
run the statement that you want to measure and subtract the average from above from the execution time

This is a good estimate for the execution time on the server, as SELECT 1 is so simple that you measure more or less just the latency between client and server.

Finding execution time of a psql query (without any connection latency/overhead)

1 Answers1