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