5

I am currently using \timing on to do some simple performance testing in Postgres. I would like to run many queries and pipe the timing results to a file. However, all of the options I have tried (\o, \l, and their command-line equivalents) pipe only the query results to a file. The Time: 1.234 ms message is not written to the file.

Is there any way I can pipe the timing output caused by \timing on into a file, or will I have to choose some other method for performing my tests?

Community
  • 1
apsillers
  • 153
  • 1
  • 5

2 Answers2

5

You can do this by piping the query into psql, then grepping the output, like so:

postgres@winterspring:~$ cat foo.sql
\timing on
select * from foo;
postgres@winterspring:~$ psql < foo.sql | grep "^Time:"
Time: 0.505 ms
postgres@winterspring:~$

Redirect just the timing to a file if needed:

postgres@winterspring:~$ psql < foo.sql | grep "^Time:" >> timing.txt

The grep will have a slight performance impact.

Philᵀᴹ
  • 31,762
  • 10
  • 83
  • 107
  • 2
    This pretty much is what I ended up doing. I didn't use grep, though; I ended up silencing all other output with \o /dev/null (to eliminate results) and \set PROMPT1 (to set the prompt to the empty string) in my .psqlrc file. – apsillers Jul 26 '12 at 02:03
  • 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:07
3

The shell function **time** would be an alternative solution on Linux / Unix systems.
To append test results to a file:

postgres@db:~$ /usr/bin/time -a -o foo psql event -c 'select 1' > /dev/null

You may need more connection options.
Discard the actual output of psql with > /dev/null.
Use the fully qualified path because - I quote man time:

Users of the bash shell need to use an explicit path in order to run the external time command and not the shell builtin variant. On system where time is installed in /usr/bin, the first example would become /usr/bin/time wc /etc/hosts

-o to provide an out file name
-a to append to the file instead of replacing.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • I wanted to avoid time if possible, since I was afraid timing a psql -c 'SELECT ...' command might return significantly different results than getting timing results from within an already-running psql query prompt using \timing. I haven't tested it, though. – apsillers Jul 26 '12 at 02:08
  • @apsillers: There is an overhead for starting up psql and connecting to the db, of course. The example with the "empty query" SELECT 1 above can give you an estimate for how much that is. For extended testing, your final solution better is smarter, though. – Erwin Brandstetter Jul 26 '12 at 19:32