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

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

From: Steve T <steve(at)retsol(dot)co(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alan Hodgson <ahodgson(at)simkin(dot)ca>, PostGreSQL <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Speeding up 'bulk' delete (and/or seeing what isgoing on while the delete is being processed)
Date: 2009-08-27 18:11:49
Message-ID: 1251396710.3634.362.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-novice
Tom/Alan,
Thanks. 300,000+ rows deleted in under 10 minutes (may have been a lot
less - I went to get a coffee, came back and it was done).


Thanks again.


PS Is there a reason that PostgreSql doesn't automatically create the
foreign key indexes?


On Thu, 2009-08-27 at 13:17 -0400, Tom Lane wrote:

> Alan Hodgson <ahodgson(at)simkin(dot)ca> writes:
> > 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.
> 
> Yeah.  The query plan itself looks perfectly reasonable, so I'm
> suspecting the problem is something happening subsequent to the row
> deletions --- like foreign key constraint checking.
> 
> 			regards, tom lane
> 



Steve Tucknott
ReTSol Ltd

DDI:         01323 488548

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2009-08-27 18:18:58
Subject: Re: Speeding up 'bulk' delete (and/or seeing what is going on while the delete is being processed)
Previous:From: Jignesh ShahDate: 2009-08-27 18:04:02
Subject: Re: Trigger for Truncate event

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