Re: Understanding EXPLAIN ANALYZE output

In article <2300.1108067885@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> writes:

> Michael Fuhr <mike@fuhr.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 \@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.