Postgresql output EXPLAIN ANALYZE to file
<p>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 <code>EXPLAIN ANALYZE</code> 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).</p>

<p>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:</p>

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

<p>but I get an error at <code>EXPLAIN</code>.</p>

<p>For the record, yes, I want to do <code>ANALYZE</code> because </p>

<ul>
<li>it reduces the amount of data to process later, and </li>
<li>it gives an actual time estimate.</li>
</ul>