Geek Answers Handbook

Postgresql EXPLAIN ANALYZE output to file

I need to know how long a particular request will take (I expect a very long time). To do this, I decided to run EXPLAIN ANALYZEin the query set with only part of the entire data set and extrapolate from there. But I have a problem; the request takes more than two hours before the connection time expires, leaving without any results. I do not want to increase the timeout, because I do not know how long it can work (from two hours to two days).

Is there any way I can get the SQL server to output data to a file on the server file system, so I don’t have to worry about timeouts? I tried the following:

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

but I get an error EXPLAIN.

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

  • it reduces the amount of data to process later and
  • gives an actual estimate of the time.
+4
source share
2 answers

The easiest 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 really want to modify the data, you shpuld to wrap it in a transaction:

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

A similar function, ready to use, probably already exists, but I did not find it.

PS: it will solve the syntax error problem, but I'm not sure if it solves the timeout problem, because, as mentioned in the documentation:

It is important . Keep in mind that the statement is actually executed when the ANALYZE option is used. Link.

+3
source

\o psql :

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

\o : , , psql .

+8

More articles:


All Articles