Search Postgresql Archives

Re: Understanding EXPLAIN ANALYZE output

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.  

Ed


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux