Re: Understanding EXPLAIN ANALYZE output

On Wed, Feb 09, 2005 at 04:34:30PM -0700, Ed L. wrote:
> Thinking about how to make this analysis faster and less labor-
> intensive ...
>
> I know of no other way to get the detailed performance data provided
> via EXPLAIN ANALYZE without just painfully disassembling a query.
> It seems it would be pretty useful w/r/t performance monitoring to
> be able to retrieve such performance numbers as those in EXPLAIN
> ANALYZE in a rowset via query.  That would seem to enable automated
> identification of things like single rows taking 4.63ms to retrieve.
> I can think of a number of application queries for which I would
> like to do this sort of analysis routinely across a bunch of
> database clusters.  I guess one could just parse the explain output
> in the meantime but, dreaming a bit here, for example,
>
>    SELECT node_id, op, parent_node_id, index, relation,
>           cost_first, cost_last, cost_rows, cost_width,
>           actual_first, actual_last, actual_rows, actual_loops,
>           index_condition
>    FROM pg_explain_analyze('SELECT * FROM foo');
>
> with output similar to
>
>  node_id |     op      | parent_node_id ...  actual_last | actual_rows | actual_loops ...
> ---------+-------------+----------------...--------------+-------------+--------------...
>       21 | Nested Loop |             20 ...        72.80 |        1014 | 1
>       22 | Nested Loop |             21 ...        46.51 |        1014 | 1
> ...
>       34 | Index Scan  |             21 ...         4.63 |           0 | 1014
> ...
>
> Then, as a routine measure, catch those cases like this one,
>
>     SELECT sql, op, index, relation, actual_first
>     FROM pg_explain_analyze('SELECT * FROM foo')
>     WHERE op = 'Index Scan'
>       AND actual_first > 1.0;
>
> Thankfully, I'm sure there are a lot of skilled postgresql'ers
> just sitting around right now wishing they had something to do.

Well, I'm a little bored; I've got tomorrow off, and this seems like
it might be doable in the kind of high-level PL/Foo's with which I'm
familiar.  What would the returning rowtype for
pg_explain_analyze(TEXT) be?

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!