Benchmarking/Performance of Postgresql Query

One way to do this is to write a function that runs an explain and then spool the output of that into a file (or insert that into a table).

E.g.:

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)'||to_explain loop
      return query select l_line, l_plan_line."QUERY PLAN";
      l_line := l_line + 1;
   end loop;
end;
$$
language plpgsql;

Then you can use generate_series() to run a statement multiple times:

select g.i as run_nr, e.*
from show_plan('select * from foo') e
  cross join generate_series(1,10) as g(i)
order by g.i, e.line_nr;

This will run the function 10 times with the passed SQL statement. The result can either be spooled to a file (how you do that depends on the SQL client you are using) or inserted into a table.

For an automatic analysis it's probably easer to use a more "parseable" explain format, e.g. XML or JSON. This is also easier to handle in the output as the plan is a single XML (or JSON) value instead of multiple text lines:

create or replace function show_plan_xml(to_explain text)
  returns xml
as
$$
begin
   return execut 'explain (analyze, verbose, format xml)'||to_explain;
end;
$$
language plpgsql;

Then use:

select g.i as run_nr, show_plan_xml('select * from foo')
from join generate_series(1,10) as g(i)
order by g.i;