How to calculate accumulated sum of query timings?

user_3175046 0
Viewed 371

I have a sql file running many queries. I want to see the accumualted sum of all queries. I know that if I turn on timing, or call 

\timing
 query 1;
 query 2;
 query 3;
 ...
 query n;

at the beginning of the script, it will start to show time it takes for each query to run. However, I need to have the accumulate results of all queries, without having to manually add them.

Is there a systematic way? If not, how can I fetch the interim times to throw them in a variable.

timing benchmarking postgresql sql database
2 Answers

The pg_stat_statements is a good module that provides a means for tracking execution statistics.

  1. First, add pg_stat_statements to shared_preload_libraries in the postgresql.conf file. To know where this .conf file exists in your filesystem, run show config_file;

    shared_preload_libraries = 'pg_stat_statements'
  2. Restart Postgres database

  3. Create the extension

    CREATE EXTENSION pg_stat_statements;

Now, the module provides a View, pg_stat_statements, which helps you to analyze various query execution metrics.

Reset the contents of stat collected before running queries.

SELECT pg_stat_statements_reset();

Now, execute your script file containing queries.

\i script_file.sql

You may get all the timing statistics of all the queries executed. To get the total time taken, simply run

select sum(total_time) from  pg_stat_statements 
    where query !~* 'pg_stat_statements';

The time you get is in milliseconds, which may be converted to desired format using various timestamp related Postgres functions

user_79985910

If you want to time the whole script, on linux or mac you can use the time utility to launch the script.

The measurement in this case is a bit more than the sum of the raw query times, because it includes some overhead of starting and running the psql command. On my system this overhead is around 20ms.

$ time psql < script.sql

…

real    0m0.117s
user    0m0.008s
sys     0m0.007s

The real value is the time it took to execute the whole script, including the aforementioned overhead.

The approach in this answer is a crude, simple client side way to measure the runtime of the overall script. It is not useful to measure milli-second precision server side execution times. It still might be sufficient for many use-cases.

The solution of Kaushik Nayak is a way more precise method to time executions directly on the server. It also provides much more insight into the execution (eg. query level times).

user_3213880
Related