Postgresql output EXPLAIN ANALYZE to file

Question

I need to know how long a particular query will run (I'm expecting a very long run time). To do this, I've decided to run an EXPLAIN ANALYZE on the query set with only a piece of the entire dataset and extrapolate from there. But I have a problem; the query takes more than two hours before the connection times out, leaving me with no results. I don't want to increase the timeout because I don't know how long might run (it's between two hours and two days).

Is there any way I can direct the SQL server to output the data to a file on the server's filesystem, so I don't have to worry about timeouts? I've tried the following:

Copy (
    EXPLAIN ANALYZE INSERT INTO <table>
    <Long complex query here>
) To '/tmp/analyze.csv' With CSV;

but I get an error at EXPLAIN.

For the record, yes, I want to do ANALYZE because

• it reduces the amount of data to process later, and
• it gives an actual time estimate.

Answer 1

You can simply use \o in psql to output results to a file:

# \o /tmp/output.txt
# explain analyze ...
# \o

\o can also pipe to a command: check out this blog post and of course the psql documentation.