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?

It's pointless when many Google hits are answers saying "use Google" - about as useful as a chocolate teapot! — NickJ
– NickJ, Commented Jul 15, 2013 at 12:14
It's one way of a couple. But still, if you tried it and it doesn't work, describe it in your question. — Jakub Matczak
– Jakub Matczak, Commented Jul 15, 2013 at 12:19
@dragoste: I think a much better place to start finding information about a product is the manual rather than a search engine. — user330315
– user330315, Commented Jul 15, 2013 at 12:35

Community · Accepted Answer · 2017-04-13 12:42:36Z

135

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.

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Jul 15, 2013 at 13:45

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

aName Over a year ago

and how to use the \timing

Jahan Over a year ago

@aName: Type \timing in psql console and hit enter. :) To disabe enter \timing again.

Dr.G · Accepted Answer · 2022-10-22 17:31:40Z

67

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"

edited Oct 22, 2022 at 17:31

Dr.G

4938 silver badges21 bronze badges

answered Dec 1, 2016 at 4:35

Terrible Coder

1,02010 silver badges10 bronze badges

1 Comment

Nulik Over a year ago

what if there are triggers, this won't work

andschar · Accepted Answer · 2021-08-27 13:13:58Z

8

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.

answered Aug 27, 2021 at 13:13

andschar

4,1622 gold badges31 silver badges38 bronze badges

Collectives™ on Stack Overflow

Postgres Query execution time

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related