Re: Understanding EXPLAIN ANALYZE output

On Thu, Feb 10, 2005 at 03:38:05PM -0500, Tom Lane wrote:
> Michael Fuhr <mike@fuhr.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/