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.

score 29 · Answer 1 · answered May 31 '16 at 15:17

29

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.

answered May 31 '16 at 15:17

ochedru

826
8
17

4

You can also write in JSON format to file with `EXPLAIN (ANALYZE, FORMAT json)` – seb Feb 27 '17 at 10:56
I couldn't find this documented anywhere (how to combine ANALYZE and FORMAT). – IAmNaN Apr 25 '19 at 20:55
1

For anyone else looking, here are [the docs for these options](https://www.postgresql.org/docs/current/sql-explain.html) – michristofides Jan 06 '20 at 15:14

Abelisto · Accepted Answer · 2016-04-11T21:42:52.247

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.

Thanks! That seems to do exactly what I needed. I do want the query to actually execute since it means less to do later. Also, I know that the query continues to run after the connection times out so I'm not worried about the explain getting cancelled. The issue was it would finish and the connection would be closed so I never saw the result. Writing to a file means I can check back later. — Thomas, Apr 11 '16 at 21:48

score 0 · Answer 3 · answered Jul 21 '23 at 01:34

0

You can also write in JSON format to file with:

EXPLAIN (ANALYZE, FORMAT JSON)

(All credit goes to @seb , I mainly answered here so I could bookmark their response)

answered Jul 21 '23 at 01:34

Riveascore

1,724
4
26
46

Postgresql output EXPLAIN ANALYZE to file

3 Answers3