Thread: 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