Is it possible to store the output of EXPLAIN into a table

On Mon, Apr 22, 2019 at 11:27:18AM -0400, Souvik Bhattacherjee wrote:

Hi,

I was just wondering if it is possible to store the output of EXPLAIN into a
table.

create temp table mytab as (select * from (explain select * from table1 where
attr = 5) t);

Unfortunately, the query above does not work.

I had to do this one and put the EXPLAIN in a function and then called
the function and captured the output, see this and following slides:

http://momjian.us/main/writings/pgsql/optimizer.pdf#page=11

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

Souvik Bhattacherjee schrieb am 22.04.2019 um 17:27:

Hi,

I was just wondering if it is possible to store the output of EXPLAIN into a table.

create temp table mytab as (select * from (explain select * from table1 where attr = 5) t);

Unfortunately, the query above does not work.

You can't do that directly. You need to write a function that wraps the explain, and returns the plan as a result set.

Something like:

create or replace function show_plan(to_explain text)
returns table (line_nr integer, line text)
as
$$
declare
l_plan_line record;
l_line integer;
begin
l_line := 1;
for l_plan_line in execute 'explain (analyze, verbose, format xml)'||to_explain loop
return query select l_line, l_plan_line."QUERY PLAN"::text;
l_line := l_line + 1;
end loop;
end;
$$
language plpgsql;

The you can use:

create temp table mytab as
select *
from show_plan('select * from table1 where attr = 5');

Souvik Bhattacherjee schrieb am 22.04.2019 um 17:27:

Hi,

I was just wondering if it is possible to store the output of EXPLAIN into a table.

create temp table mytab as (select * from (explain select * from table1 where attr = 5) t);

Unfortunately, the query above does not work.

You can't do that directly. You need to write a function that wraps the explain, and returns the plan as a result set.

Something like:

create or replace function show_plan(to_explain text)
returns table (line_nr integer, line text)
as
$$
declare
l_plan_line record;
l_line integer;
begin
l_line := 1;
for l_plan_line in execute 'explain (analyze, verbose, format xml)'||to_explain loop
return query select l_line, l_plan_line."QUERY PLAN"::text;
l_line := l_line + 1;
end loop;
end;
$$
language plpgsql;

The you can use:

create temp table mytab as
select *
from show_plan('select * from table1 where attr = 5');

Souvik Bhattacherjee <kivuosb@gmail.com> writes:

I was just wondering if it is possible to store the output of EXPLAIN into
a table.

EXPLAIN won't do that directly, but you could make a plpgsql function
along the lines of

for t in execute explain ...
return next t;

(too lazy to check the exact details here, but I believe you can find
related examples in our regression tests) and then store the function's
result into a table.

regards, tom lane

Thanks, works fine!

-Souvik

On Mon, Apr 22, 2019 at 12:02 PM Tom Lane <tgl@sss.pgh.pa.us> wrote: