Re: Understanding EXPLAIN ANALYZE output

In article <20050211115856.GB7055@svana.org>,
Martijn van Oosterhout <kleptog@svana.org> writes:

> To be honest, I'm not sure this a real problem. You could simply label
> the first columns a rownumber and a depth number.

[See below ]

> At the moment people are talking about parsing strings to get the
> output. That output has the same issues as what's being proposed here,
> we're just saving the parsing step.

Yes, but whenever I need to parse "prose", I think there's something
wrong.  The textual EXPLAIN output is fine only for short query plans.

> However, tuple based output would be quite unreadable for humans, how
> can one specify which output to return. EXPLAIN ANALYZE WITH TUPLES
> query?

Going from tuples to prose is easy; there could be several formatting
functions for that - maybe even one which outputs the plan in a way
compatible to Oracle or something else.

If someone's interested, here's my "prose parser" again, now also
calculating the nesting depth:

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

CREATE OR REPLACE FUNCTION pg_explain(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;
  my @plen = (0);
  while (my $res = $sth->fetchrow_arrayref) {
    $cnt++;
    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;
    unless (@a) {
      $_ = $res->[0];
      next if /^Total runtime: \d+\.\d+ ms$/;
      s/^\s+//;
      $res[$#res]->{cont} = $_;
      next;
    }
    my $pref = shift @a || "";
    my $pl = length $pref;
    # Process prefix
    my $lvl = 0;
    if ($pl > $plen[$#plen]) {
      push @plen, $pl;
      $lvl = $#plen;
    } else {
      for my $ix (0 .. $#plen) {
        next unless $plen[$ix] == $pl;
        $lvl = $ix;
        last;
      }
    }
    my %elt = (id => $cnt, level => $lvl);
    $elt{$nm[$_]} = $a[$_] for (0..$#nm);
    push @res, \%elt;
  }
  $dbh->disconnect;
  return \@res;
$$ LANGUAGE "plperlu";