Re: Understanding EXPLAIN ANALYZE output

In article <20050211115856(dot)GB7055(at)svana(dot)org>,
Martijn van Oosterhout <kleptog(at)svana(dot)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 \(at)res;
$$ LANGUAGE "plperlu";