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
3 Answers
Just turn on timing by entering:
\timing
-
2From out bash, the "-c" option of
psql
does 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
\timing
to~/.psqlrc
if 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
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 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 ofanalyze
that adds to the query time. Based on the docs, I think thatEXPLAIN (ANALYZE, TIMING OFF) SELECT foo FROM bar
will give you more useful timing information. See postgresql.org/docs/9.6/static/sql-explain.html for details.– larsksDec 5, 2017 at 14:53