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

[Thomas Kellerer]

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');