Обсуждение: odd explain diagram in head versions
I tried sending this with the screenshot attached but it did not seem to go through, so I am sending again without. The screenshot can be found at http://postgresql.jdrake.com/pgadmin/pgadmin-explain-delete-fkconstraint.png ---------- Forwarded message ---------- Date: Wed, 28 Mar 2007 23:16:48 -0700 (PDT) From: Jeremy Drake <pgsql@jdrake.com> To: pgadmin-hackers@postgresql.org Subject: odd explain diagram in head versions First off, let me apologize if I am sending this to the wrong list. I figure, since I am running svn head of pgAdmin against cvs head of PostgreSQL, rough edges are to be expected, and it would do more good to make sure the hackers are aware of them than to send some bug report. I have been enjoying the explain graph functionality of pgadmin for a couple weeks now, and wishing that I had known about it a couple years ago. I think it is really great, and would like to thank the authors of it. I have a CVS head postgres server, and a database which has foreign key constraints with ON DELETE CASCADE. I wrote a delete query against the referred-to table, and hit the explain function in pgadmin. The attached screenshot was the result. The data output for this looked like: "Index Scan using programme_prog_times_idx on programme (cost=0.00..3310.40 rows=2121 width=6) (actual time=15.739..25.609rows=2353 loops=1)" " Index Cond: (prog_start >= '2007-04-09 16:45:00-07'::timestamp with time zone)" "Trigger for constraint credits_prog_id_fkey: time=161.238 calls=2353" "Trigger for constraint programme_descs_prog_id_fkey: time=154.521 calls=2353" "Trigger for constraint programme_ratings_prog_id_fkey: time=119.386 calls=2353" "Total runtime: 484.351 ms" The problem seems to be that the trigger information is overlaid on the index scan in the graph, resulting in an unreadable mess of text. -- Predestination was doomed from the start.
Jeremy Drake wrote > > The problem seems to be that the trigger information is overlaid on the > index scan in the graph, resulting in an unreadable mess of text. > Looks like it. Can you provide me a self contain test case please? Thanks, Dave
On Thu, 29 Mar 2007, Dave Page wrote: > Jeremy Drake wrote > > > > The problem seems to be that the trigger information is overlaid on the > > index scan in the graph, resulting in an unreadable mess of text. > > > > Looks like it. Can you provide me a self contain test case please? Sure. Here's a simple schema to demonstrate the problem: CREATE TABLE tbl1 ( tbl1id integer NOT NULL PRIMARY KEY ); CREATE TABLE tbl2 ( tbl2id integer NOT NULL PRIMARY KEY, tbl1id integer NOT NULL REFERENCES tbl1(tbl1id) ON DELETE CASCADE ); INSERT INTO tbl1(tbl1id) SELECT x from generate_series(0,4) t(x); INSERT INTO tbl2(tbl1id, tbl2id) SELECT tbl1id, x + tbl1id*5 FROM tbl1, generate_series(0,4) t(x); In the query window, make sure the explain analyze option is on and ask it to explain the query: DELETE FROM tbl1 WHERE tbl1id BETWEEN 1 AND 3; -- There are people so addicted to exaggeration that they can't tell the truth without lying. -- Josh Billings
Jeremy Drake wrote: > On Thu, 29 Mar 2007, Dave Page wrote: > >> Jeremy Drake wrote >>> The problem seems to be that the trigger information is overlaid on the >>> index scan in the graph, resulting in an unreadable mess of text. >>> >> Looks like it. Can you provide me a self contain test case please? > > Sure. Here's a simple schema to demonstrate the problem: Thanks - I've committed a fix for this. What was actually happening was that in ANALYZE mode, the runtime of any triggers is appended to the plan for info. The canvas was trying to add these lines as extra nodes, but because they aren't really part of the plan couldn't position or render them properly, resulting in the mess of text you reported. It now ignores any Trigger lines. Regards, Dave.