Postgresql output EXPLAIN ANALYZE to file

问题:

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.

回答1:

The very simple trick:

create or replace function get_explain(in qry text, out r text) returns setof text as $$
begin
  for r in execute qry loop
    raise info '%', r;
    return next;
  end loop;
  return;
end; $$ language plpgsql;

Note that if you don't want to really modify the data then you shpuld to wrap it into the transaction:

begin;
copy (select get_explain('explain (analyze) select 1;')) to '/tmp/foo.foo';
select get_explain('explain (analyze, format xml) select 1;');
rollback;

Probably the ready to use similar function already exists but I not found it.

PS: It will solve the problem with syntax error but I not sure that it solves the timeout problem, because as mentioned in the documentation:

Important: Keep in mind that the statement is actually executed when the ANALYZE option is used. Link.

回答2:

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.