Re: Understanding EXPLAIN ANALYZE output

In article <20050211222117.GF7055@svana.org>,
Martijn van Oosterhout <kleptog@svana.org> writes:

> There's one corner case you need to make sure you handle. In the plan
> that started this thread there's a query node marked (never executed).
> That will affect yout regex a bit. an that case you should probably
> return NULLs. (It might do that, I havn't run the code through
> carefully).

Ah, good point.  Here's a version which should do that.  It also tries
to extract the index name.

------------------------ snip snip snipety-snip --------------------------

CREATE TYPE expl_t AS (
  id INT,
  level INT,
  type TEXT,
  indx 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_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 indx 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
      (?:\susing\s(.+?))?           # Index Scan using ixname
      (?:\son\s(\S+)(?:\s\S+)?)?    # on table [alias]
      \s\s                          # Estimations:
      \(cost=(\d+\.\d+)\.\.(\d+\.\d+)\srows=(\d+)\swidth=(\d+)\)
      \s                            # Actual values:
      \((.+?)\)
    $}x;
    unless (@a) {
      $_ = $res->[0];
      next if /^Total runtime: \d+\.\d+ ms$/;
      s/^\s+//;
      if (defined $res[$#res]->{cont}) {
        $res[$#res]->{cont} .= ", $_";
      } else {
        $res[$#res]->{cont} = $_;
      }
      next;
    }
    my @x = $a[8] =~ m{actual\stime=(\d+\.\d+)\.\.(\d+\.\d+)\srows=(\d+)\sloops=(\d+)$};
    splice @a, 8, 1, @x;
    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";