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

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.