Explain - inserts only one row

Question

I am trying to manually save the optimization plan for further analysis, for example:

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 that it only saved the first line from the explanation statement:

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

How can I do this to save the whole plan?

Answer 1

Because explain cannot be used, for example, for example. a SELECT is a bit complicated, and for that you need dynamic SQL.

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 an instruction to be explained in a line makes things a little more complicated.

If I needed this regularly, 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 ...');