Re: Understanding EXPLAIN ANALYZE output

In article <2300(dot)1108067885(at)sss(dot)pgh(dot)pa(dot)us>,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Michael Fuhr <mike(at)fuhr(dot)org> writes:
>> On Wed, Feb 09, 2005 at 11:36:22PM -0500, Tom Lane wrote:
>>> (And no, EXECUTE doesn't help.) This seems like an oversight. We
>>> already have some understanding in the backend that certain utility
>>> commands return query results; the SPI code should be letting those
>>> results be scanned as if they were ordinary SELECT results.

>> Any suggestions for the meantime?

> Update to CVS tip ;-)

The faint-hearted could use a separate connection instead; something
like that:

CREATE TYPE expl_t AS (
id INT,
type TEXT,
relation TEXT,
cost1 FLOAT,
cost2 FLOAT,
rows INT,
width INT,
atime1 FLOAT,
atime2 FLOAT,
arows INT,
loops INT
);

CREATE OR REPLACE FUNCTION pg_explain_analyze(TEXT) RETURNS SETOF expl_t AS $$
my $sql = $_[0];
my $rv = spi_exec_query('SELECT current_database()');
my $db = $rv->{rows}[0]->{current_database};
# Grab EXPLAIN output
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect("DBI:Pg:dbname=$db", "", "", {
AutoCommit => 0,
PrintError => 0,
RaiseError => 1,
});
my $sth = $dbh->prepare("EXPLAIN ANALYZE $sql");
$sth->execute();
my @res = ();
my @nm = qw(type relation cost1 cost2 rows width atime1 atime2 arows loops);
my $cnt = 0;
while (my $res = $sth->fetchrow_arrayref) {
my @a = $res->[0] =~ m{
^(?:\s+->\s\s)? # Prefix
(\S+(?:\s\S+)?) # Operation
(?:\son\s(\S+)(?:\s\S+)?)? # on table [alias]
\s\s # Estimations:
\(cost=(\d+\.\d+)\.\.(\d+\.\d+)\srows=(\d+)\swidth=(\d+)\)
\s # Actual values:
\(actual\stime=(\d+\.\d+)\.\.(\d+\.\d+)\srows=(\d+)\sloops=(\d+)\)$
}x or next;
my %elt = (id => ++$cnt);
$elt{$nm[$_]} = $a[$_] for (0..$#nm);
push @res, \%elt;
}
$dbh->disconnect;
return \(at)res;
$$ LANGUAGE "plperlu";

This happily ignores index conditions etc for now, but it might be a start.

But I think it should be the other way round: EXPLAIN ANALYZE should
return its output in table form, and there should be a function to
convert that table into what EXPLAIN ANALYZE outputs now.