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...constraint.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.609 rows=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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq