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@retsol.co.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