Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
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 2:03
add comment

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.

share|improve this answer
    
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 2: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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.