Postgres Query execution time

Question

In the MySQL command line interface, when you execute a query it will tell you how long the query took to execute after printing out the results.

In the Postgres command line interface (psql) it does not tell you. I know how to configure the logging so I can get the information from logs, but it would be more convenient to have it print to standard output like it does in MySQL.

Can this be done?

Answer 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.

Answer 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"