CODEWITHSUNDEEP
postgresql
NickJ
NickJ

Reputation: 9559

Postgres Query execution time

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?

Upvotes: 78

Views: 159624

Answers (3)

Terrible Coder
Terrible Coder

Reputation: 980

I think that EXPLAIN ANALYZE might be helpful to you

Syntax:

EXPLAIN ANALYZE query;

Example;

EXPLAIN ANALYZE 
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"

Upvotes: 63

andschar
andschar

Reputation: 3973

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.

Upvotes: 8

Craig Ringer
Craig Ringer

Reputation: 324375

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.

Upvotes: 132

Related Questions