Re: Speeding up 'bulk' delete (and/or seeing what is going on while the delete is being processed)

Looks like that's the cause.
I had wrongly assumed that the constraint itself caused the foreign
table to be indexed.

On Thu, 2009-08-27 at 09:34 -0700, Alan Hodgson wrote:

> On Thursday 27 August 2009, Steve T <steve(at)retsol(dot)co(dot)uk> wrote:
> > explain delete from supplierProduct
> > where supplierrecno = 1
> > and recNo in
> > (select recNo from _sub_recs);
> >
> > The problem is that the query, when run in earnest, disappears off into
> > the sunset for hours. How can I see how it is progressing (is the only
> > way setting up a procedure and doing explicit transactions for each
> > single delete)? I also tried 'delete .... using' - but that gave the
> > same plan.
> >
> > Is is better to use the IN format above or EXISTS?
> >
>
> "exists" in 8.1, I believe. "in" in later versions.
>
> > All the above is being run directly in psql.
>
> Do you have other foreign keys into supplierProduct on other tables where
> the relevant columns aren't indexed? Slow deletes are usually caused by
> that.
>

Steve Tucknott
ReTSol Ltd

DDI: 01323 488548