Skip site navigation (1) Skip section navigation (2)

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

From: Alan Hodgson <ahodgson(at)simkin(dot)ca>
To: PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Speeding up 'bulk' delete (and/or seeing what is going on while the delete is being processed)
Date: 2009-08-27 16:34:33
Message-ID: 200908270934.33883@hal.medialogik.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2009-08-27 17:17:56
Subject: Re: Speeding up 'bulk' delete (and/or seeing what is going on while the delete is being processed)
Previous:From: Steve TDate: 2009-08-27 15:27:18
Subject: Speeding up 'bulk' delete (and/or seeing what is going on whilethe delete is being processed)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group