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

From Steve T
Subject Re: Speeding up 'bulk' delete (and/or seeing what is going on while the delete is being processed)
Date
Msg-id 1251394709.3634.359.camel@localhost.localdomain
Whole thread Raw
In response to Re: Speeding up 'bulk' delete (and/or seeing what is going on while the delete is being processed)  (Alan Hodgson <ahodgson@simkin.ca>)
List pgsql-novice
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

pgsql-novice by date:

Previous
From: Jignesh Shah
Date:
Subject: Re: Trigger for Truncate event
Next
From: Thomas Kellerer
Date:
Subject: Re: Trigger for Truncate event