imitatio creatio

Same as depesz and Greg, but using user-defined function:


CREATE FUNCTION ea_totals(text) RETURNS text AS $body$
my $result;
my $com = shift;
$com =~ /^\s*explain\s+/i or die qq{Not an explain query\n};
my $rv = spi_exec_query($com);
$rv->{status} eq 'SPI_OK_UTILITY' or die qq{Not a proper explain?\n};
for (map { $_->{'QUERY PLAN'} } @{$rv->{rows}}) {
my $string = ' ' x 10;
if ( /actual time=\d+\.\d*\.\.(\d+\.\d*) rows=\d+ loops=(\d+)/ ) {
$string = sprintf("%10.1f", $1 * $2);
}
$result .= "$string $_ \n";
}
return $result;
$body$ LANGUAGE plperl;


Usage:

SELECT * FROM ea_totals( $$ explain analyze query here $$);