How can I time SQL-queries using psql?

Question

I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?

For more details on benchmarking PostgreSQL queries: http://dba.stackexchange.com/q/42012/9622 — Franck Dernoncourt, May 15 '13 at 22:29

score 479 · Accepted Answer · edited Feb 17 '15 at 16:10

479

Just turn on timing by entering:

\timing

edited Feb 17 '15 at 16:10

Alison R.

103
3

answered Jun 03 '11 at 10:39

Caleb

5,326
1
17
8

2
From out bash, the "-c" option of psql does not allow this setting. You can do this with something like:
```
\timing

select 1

EOF
```
– 3manuek Jul 14 '16 at 17:38
22

Or do psql -c '\timing' -c 'select 1' – cdmckay Aug 24 '17 at 02:11
10

Add \timing to ~/.psqlrc if you want it enabled each time you start the client. – Joel Harris Jan 24 '20 at 16:02
To enable timing for all your psql sessions, run this in your shell (note the double backslash): echo '\\timing on' >> ~/.psqlrc – Timur Shtatland Apr 25 '23 at 20:05

Jack Douglas · Answer 2 · 2018-07-16T21:31:49.690

80

Timing can be turned on with \timing at the psql prompt (as Caleb already said).

If you are on 8.4 or above, you can add an optional on/off argument to \timing, which can be helpful if you want to be able to set timing on in .psqlrc - you can then set \timing on explicitly in a script where plain \timing would otherwise toggle it off

edited Jul 16 '18 at 21:31

answered Jun 05 '11 at 20:37

Jack Douglas

39,869
15
101
176

Devi · Answer 3 · 2017-11-17T07:01:10.430

35

The time that \timing returns also includes the network latency, if you're connecting to a remote server.

When you don't want that and don't need the query output too, better use EXPLAIN ANALYZE, which outputs the query plan with the planner estimates plus the actual execution times.

for example, EXPLAIN ANALYZE SELECT foo from bar ;

edited Nov 17 '17 at 07:01

answered Nov 17 '17 at 04:31

Devi

501
5
5

1

... the only issue being that you don't receive the normal query output. – András Váczi Nov 17 '17 at 04:56
12

Using explain analyze yields times that are approximately double what I see when using \timing, which is the opposite of what I would expect based on the comments here regarding network latency. I suspect that there is overhead in the normal execution of analyze that adds to the query time. Based on the docs, I think that EXPLAIN (ANALYZE, TIMING OFF) SELECT foo FROM bar will give you more useful timing information. See https://www.postgresql.org/docs/9.6/static/sql-explain.html for details. – larsks Dec 05 '17 at 14:53

How can I time SQL-queries using psql?

3 Answers3

Linked