Explain - inserts only one row

Question

I'm trying to manually save optimizer plan for further analysis, like this:

do $$
declare
tmp text;
begin
  explain
    select * from public.some_table where 1=2 into tmp;
  insert into public.plans(plan) values (tmp);
end; $$

But when I select it later, I see it only saved first row from the explain statement:

Result  (cost=0.00..82.97 rows=1 width=114)

How can I make it to save the whole plan?

Answer 1

Because explain cannot be used like e.g. a SELECT this is a bit tricky and you need dynamic SQL for this.

The following worked for me:

do
$$
declare
   plan_line record;
begin
   for plan_line in execute 'explain select * from public.some_table where 1=2' loop
      insert into plans values (plan_line."QUERY PLAN");
   end loop;
end;
$$

Having the statement to be explained in a string makes things a bit more complicated.

If I needed that on a regular basis, I would probably create a function that does this:

create or replace function explain(to_explain text)
  returns setof text
as
$$
declare
  plan_line record;
begin
   for plan_line in execute 'explain '||to_explain loop
      return next plan_line."QUERY PLAN";
   end loop;
end;
$$
language plpgsql;

Then you can do something like:

insert into plans 
select * 
from explain('select ...');