Thread: Q: explain on delete

Hi,

today I was trying to figure out, what's going on during a delete on a table
with several foreign key triggers (and "on delete cascade"). But, as found in
the achives,
"EXPLAIN on a delete isn't very interesting.."
because it doesn tell me, what going on behind the scenes, where the triggers
are running.

So my question is: Is there another way besides "explain" to get information
out of postgresql (7.3.1) what's going on during a "delete" on a table with
many foreign key triggers .

The only other option what comes to my mind is to inspect the foreign key
dependencies and check manually, if some index is missing (But I was hoping,
the maching could tell me that...).

Best regards and thanks a lot,

    Tilo

Tilo Schwarz <mail@tilo-schwarz.de> writes:
> So my question is: Is there another way besides "explain" to get information
> out of postgresql (7.3.1) what's going on during a "delete" on a table with
> many foreign key triggers .

IIRC, you can set log_statement=on before the first time you run the
DELETE in a given session, and then look in the postmaster log for
copies of the SQL statements that are generated by the foreign key
mechanism.  It's not great, particularly because you can't conveniently
see the associated plans, but it's better than nothing ...

            regards, tom lane

On Mon, Feb 03, 2003 at 05:01:17PM -0500, Tom Lane wrote:

> mechanism.  It's not great, particularly because you can't conveniently
> see the associated plans, but it's better than nothing ...

But you can take the thereby-discovered queries and pass them to
EXPLAIN manually.  It's a bit of a pain, but this is how I have found
some real performance problems.  (Sometimes it just leaps out at
you, actually, when you see the query.)

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Andrew Sullivan <andrew@libertyrms.info> writes:
> On Mon, Feb 03, 2003 at 05:01:17PM -0500, Tom Lane wrote:
>> mechanism.  It's not great, particularly because you can't conveniently
>> see the associated plans, but it's better than nothing ...

> But you can take the thereby-discovered queries and pass them to
> EXPLAIN manually.  It's a bit of a pain, but this is how I have found
> some real performance problems.

Actually, the problem with looking at the plan for an FK query is that
the query will involve parameters ($n), and so you can't just stick it
into EXPLAIN as-is.  (Substituting constants for the $n symbols does
*not* necessarily give the same plan, although it may be close enough
to give you a clue about major problems like needing to add an index.)

As of CVS tip there's finally a real solution: you can PREPARE the
parameterized query and then EXPLAIN EXECUTE it.  For example,
given something like

    SELECT * FROM foo WHERE col = $1

you could do

    PREPARE q1(int) AS SELECT * FROM foo WHERE col = $1
    EXPLAIN EXECUTE q1(42)

            regards, tom lane

On Tue, Feb 04, 2003 at 11:24:03AM -0500, Tom Lane wrote:

> *not* necessarily give the same plan, although it may be close enough
> to give you a clue about major problems like needing to add an index.)

Yes, this was what I meant.  Sorry, I should have been clearer.  What
I find frequently, actually, is that it more often makes you notice
something about the distribution of data.  The cases which really
kill you with FKs are (a) there is contention on one of the tables,
so that you end up with everything waiting for their turn at the lock
and (b) referenced tables which are really big but have a small
number of values.  Case (b) is interesting, because it's possible to
(mis)design a system which never encounters the symptom directly, and
only runs into it with a trigger.

> As of CVS tip there's finally a real solution: you can PREPARE the
> parameterized query and then EXPLAIN EXECUTE it.  For example,

Oh, _nice_.  I look forward to that.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Tom Lane writes:

> As of CVS tip there's finally a real solution: you can PREPARE the
> parameterized query and then EXPLAIN EXECUTE it.  For example,
> given something like
>
>     SELECT * FROM foo WHERE col = $1
>
> you could do
>
>     PREPARE q1(int) AS SELECT * FROM foo WHERE col = $1
>     EXPLAIN EXECUTE q1(42)

Nice, I'll try that...

Regards,

    Tilo