Re: Understanding EXPLAIN ANALYZE output

David Fetter wrote:

>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?
>
>
You could return it as formatted text. if you want to make it simple.

J



>Cheers,
>D
>
>


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL