Postgres Query execution time

Solution 1:

Use \timing as explained by "How can I time SQL-queries using psql?".

See also the manual for psql.

If you want server-side execution times that don't include the time to transfer the result to the client, you can set log_min_duration_statement = 0 in the configuration, then SET client_min_messages = log so you get the log info in the console.

You can also use EXPLAIN ANALYZE to get detailed execution timings. There's some timing overhead for this unless you use EXPLAIN (ANALYZE TRUE, TIMING FALSE), which is only in newer versions, and disables detailed timing to give only an aggregate execution time instead.

PgBadger, especially when combined with the auto_explain module, can provide useful aggregate statistics from log analysis.

Finally, there's pg_stat_statements, which can collect handy aggregate information on the running system.

Solution 2:

I think that EXPLAIN ANALYSE might be helpful to you

Syntax:

EXPLAIN ANALYSE query;

Example;

EXPLAIN ANALYSE 
SELECT  * 
FROM    demotable;

Output:

"Seq Scan on demotable  (cost=0.00..12.10 rows=210 width=356) 
                        (actual time=0.020..0.021 rows=8 loops=1)"
"Planning time: 18.477 ms"
"Execution time: 0.042 ms"

Solution 3:

You can use \timing in pgsql as follows to have the execution time printed to standard output:

psql -d db -c '\timing' -c 'select 1'

Taken from this comment.