Re: Q: explain on delete

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