Re: Understanding EXPLAIN ANALYZE output

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