Обсуждение: Explain analyze gives no info

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?

Mike

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

Richard Huxton <dev@archonet.com> writes:
> Relyea, Mike wrote:
>> 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.

Yeah.  CVS-tip EXPLAIN ANALYZE will at least tell you how much time was
chewed by each trigger, but it still sees each trigger as a black box.

However, I don't think we need to guess very hard about what's going on
here.  Obviously, one of the ON DELETE foreign key triggers is scanning
the referencing table with a seqscan, which probably means that either
there's no index at all on the referencing column, or the referencing
column is of a different datatype than the master.  Look through the
tables that reference this one and check the indexes and datatypes.

            regards, tom lane