Re: Understanding EXPLAIN ANALYZE output

On Thu, Feb 10, 2005 at 03:38:05PM -0500, Tom Lane wrote:
> Michael Fuhr <mike(at)fuhr(dot)org> writes:
>
> > Any suggestions for the meantime?
>
> Update to CVS tip ;-)

Done. Below is a simple proof of concept for an explain() function
in PL/pgSQL. It's not necessarily correct -- it just shows what
could be done.

CREATE TYPE explain_in AS (
exp text
);

CREATE TYPE explain_out AS (
pname text,
startup_cost numeric(12,2),
total_cost numeric(12,2),
plan_rows integer,
plan_width integer
);

CREATE FUNCTION explain(query text) RETURNS SETOF explain_out AS $$
DECLARE
row explain_in;
ret explain_out;
BEGIN
FOR row IN EXECUTE 'EXPLAIN ' || query LOOP
ret.pname := substring(row.exp FROM '([^\\(]+) \\(');
ret.startup_cost := substring(row.exp FROM 'cost=(\\d+\\.\\d+)');
ret.total_cost := substring(row.exp FROM '\\.\\.(\\d+\\.\\d+) rows');
ret.plan_rows := substring(row.exp FROM ' rows=([[:digit:]]+)');
ret.plan_width := substring(row.exp FROM ' width=([[:digit:]]+)');

IF ret.plan_rows IS NOT NULL THEN
RETURN NEXT ret;
END IF;
END LOOP;

RETURN;
END;
$$ LANGUAGE plpgsql VOLATILE STRICT;

SELECT * FROM explain('SELECT count(*) FROM pg_class');
pname | startup_cost | total_cost | plan_rows | plan_width
-----------------------------+--------------+------------+-----------+------------
Aggregate | 26.69 | 26.69 | 1 | 0
-> Seq Scan on pg_class | 0.00 | 24.95 | 695 | 0
(2 rows)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/