How to calculate accumulated sum of query timings?

Question

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.

Answer 1

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