Colocando os resultados do EXPLAIN em uma tabela
Há uma discussão na lista hackers sobre a explicação de planos, e alguém mencionou a idéia de guardar a informação do explain em rabelas. Isso me lembrou de uma pequena função Pl/Perlu que eu demonstrei como parte da minha palestra de Pl/Perl na PGcon. Eu estava usando-a para demonstrar um conceito em Pl/Perl, mas acabei usando outra função, então a função abaixo é ainda um rascunho sem polimento. Tavez alguém a ache útil de qualquer forma :-). A primeira coisa que precisamos é criar duas tabelas, uma que irá receber os resultados do explain de um plano, e a outra que irá conter todos os sub-elementos de cada plano:
CREATE SEQUENCE explain_result_id_seq;
CREATE TABLE explain_result (
id INTEGER NOT NULL DEFAULT nextval('explain_result_id_seq'),
CONSTRAINT explain_result_id_pk PRIMARY KEY (id),
query TEXT NOT NULL,
cdate TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE explain_row (
query INT REFERENCES explain_result(id) ON DELETE CASCADE,
orderin SMALLINT NOT NULL,
indent SMALLINT,
action TEXT,
cost_start FLOAT,
cost_end FLOAT,
total_cost FLOAT,
row1 INTEGER,
loop INTEGER,
actual_start FLOAT,
actual_end FLOAT,
total_actual FLOAT,
row2 INTEGER,
width INTEGER
);
Agora, a própria função, escrita em Pl/Perlu. Apenas passe uma declaração explain ou explain analyze, e a função irá executá-lo e tentar interpretá-lo, salvando o resultado nas tabelas. Ela retorna o id da linha inserida na tabela explain_result. Observe que essa função requer a versão 8.2 devido à cláusula RETURNING no INSERT, mas deve ser fácil modificá-la para funcionar com versões anteriores também.
CREATE FUNCTION plp_explain(TEXT)
RETURNS TEXT
LANGUAGE plperlu
AS $_$
use strict;
use warnings;
my $com = shift;
$com =~ /^\s*explain\s+/i or die qq{Not an explain query\n};
my $rv = spi_exec_query($com);
$rv->{status} eq 'SPI_OK_UTILITY' or die qq{Not a proper explain?\n};
my $SPACER = qr{(\s*(?:->)*\s*)};
my $FLOAT = qr{\d+\.\d+};
my $COST = qr{\(cost=($FLOAT)\.\.($FLOAT) rows=(\d+) width=(\d+)\)};
my $ACTUAL = qr{\(actual time=($FLOAT)\.\.($FLOAT) rows=(\d+) loops=(\d+)\)};
my $ACTUALROW = qr{^$SPACER(.+)$COST\s*$ACTUAL$};
my $COSTROW = qr{^$SPACER(.+)$COST$};
my $RAWROW = qr{^$SPACER(.+)\s*$};
## Returning needs 8.2
my $SQL = 'INSERT INTO explain_result(query) VALUES ($1) RETURNING id';
my $sth = spi_prepare($SQL,'text');
my $res = spi_exec_prepared($sth,$com);
my $id = $res->{rows}[0]{id};
$SQL = "INSERT INTO explain_row
(query,orderin,indent,action,cost_start,cost_end,total_cost,row1,loop,actual_start,actual_end,total_actual,row2,width)
VALUES ($id
";
my $orderin = 1;
for my $row (map { $_->{'QUERY PLAN'} } @{$rv->{rows}}) {
$row =~ /$ACTUALROW/
or $row =~ /$COSTROW/
or $row =~ /$RAWROW/
or die "Cannot parse explain row: $row";
my ($indent,$action) = ($1,$2);
my ($c1,$c2,$row1,$loop,$a1,$a2,$row2,$width) =
('NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL');
if (defined $3) {
($c1,$c2,$row1,$loop) = ($3,$4,$5,$6);
}
if (defined $7) {
($a1,$a2,$row2,$width) = ($7,$8,$9,$10);
}
$action =~ s/'/''/g;
my $sql = "$SQL, $orderin,";
$orderin++;
$sql .= (length $indent).qq{,'$action',$c1,$c2,0,$row1,$loop,$a1,$a2,0,$row2,$width)};
spi_exec_query($sql);
}
return "Explain plan stored as id $id\n";
$_$;
O uso é dessa forma:
SELECT plp_explain($$
EXPLAIN ANALYZE SELECT nspname, relname, pg_size_pretty(pg_relation_size(c.oid))
FROM pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid
AND relkind IN ('r','i')
ORDER BY pg_relation_size(c.oid) DESC
LIMIT 50
$$);
-- Take a look at the results:
\x
SELECT FROM explain_result;
SELECT FROM explain_row;
Poderia certamente ser um pouco mais refinado, por exemplo, interpretando e separando em seções conhecidas (Hash, Seq Scan, etc.) mas é um bom início até que o Postgres desenvolva uma funcionalidade similar nativamente.
