I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql
?
Asked
Active
Viewed 2e+01k times
332
![](../../users/profiles/106.webp)
Jonas
- 31,495
- 27
- 59
- 64
-
5For more details on benchmarking PostgreSQL queries: http://dba.stackexchange.com/q/42012/9622 – Franck Dernoncourt May 15 '13 at 22:29
3 Answers
455
-
2From out bash, the "-c" option of `psql` does not allow this setting. You can do this with something like: ```psql --o /dev/null <
– 3manuek Jul 14 '16 at 17:38 -
20
-
10Add `\timing` to `~/.psqlrc` if you want it enabled each time you start the client. – Joel Harris Jan 24 '20 at 16:02
78
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
![](../../users/profiles/1396.webp)
Jack Douglas
- 38,607
- 15
- 97
- 174
33
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 ;
![](../../users/profiles/69835.webp)
Devi
- 481
- 5
- 5
-
1... the only issue being that you don't receive the normal query output. – dezso Nov 17 '17 at 04:56
-
10Using `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