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@xxxxxxxxxxxxxxxxx - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:jd@xxxxxxxxxxxxxxxxx
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
