Re: Explain analyze gives no info

Relyea, Mike wrote:
> I am trying to optimize a delete query that's currently taking 4 hours
> to run.  My first step was to add some indexes on some of my FK's.  That
> sped up my select queries by as much as ~20x.  From 3.5 sec to < 0.25
> sec.  They didn't help my delete query.  I ran an explain analyze on the
> delete query and the records got deleted after 4 hours but it never
> showed me the query plan.  I'm using 8.0.1 on WinXP accessing it with
> pgAdminIII.
>
> FWIW, the DB has a lot of FK's and some of them (the ones on my big
> tables 1-10 million records) have ON DELETE CASCADE triggers.
>
> Any suggestions for how to get the explain analyze output?

You should get *some* output. Unfortunately, I don't think it will show
you anything useful. The effort is almost certainly all going on the
FK's and you can't see through the trigger code to see what's happening
there.

If this is an occasional, manual type thing it can be quicker to:
  1. start transaction
  2. lock relevant tables
  3. delete from dependant tables
  4. delete from "main" table
  5. commit

Of course, its practicality depends on how straightforward it is to word
the WHERE clause in step 3.

--
   Richard Huxton
   Archonet Ltd