Postgresql – Pipe psql timing to file

performanceperformance-testingpostgresql

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?

Best Answer

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.

Related Question