I would like to benchmark some SQL-queries agains my PostgreSQL database. Is there any way I can time SQL-queries using psql?
-
5For more details on benchmarking PostgreSQL queries: dba.stackexchange.com/q/42012/9622– Franck DernoncourtMay 15, 2013 at 22:29
Add a comment
|
3 Answers
Just turn on timing by entering:
\timing
-
2From out bash, the "-c" option of
psqldoes not allow this setting. You can do this with something like:psql --o /dev/null <<EOF \timing select 1 EOF– 3manuekJul 14, 2016 at 17:38 -
18
-
10Add
\timingto~/.psqlrcif you want it enabled each time you start the client. Jan 24, 2020 at 16:02
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
answered Jun 5, 2011 at 20:37
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 ;
-
1... the only issue being that you don't receive the normal query output.– dezsoNov 17, 2017 at 4:56
-
9Using
explain analyzeyields 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 ofanalyzethat adds to the query time. Based on the docs, I think thatEXPLAIN (ANALYZE, TIMING OFF) SELECT foo FROM barwill give you more useful timing information. See postgresql.org/docs/9.6/static/sql-explain.html for details.– larsksDec 5, 2017 at 14:53