Re: Poor Delete performance

There is a foreign key constraint, but that is accomplished by an
INSERT/UPDATE trigger. I don't see why that should cause any problems.

--
Bill

On Mon, Mar 12, 2001 at 08:27:51AM -0800, Stephan Szabo wrote:
>
> As an outside chance, does the table in question have any constraints
> defined on it?
>
> On Mon, 12 Mar 2001, Bill Huff wrote:
>
> > I am having some performance issues with deletion.
> >
> > It appears that in deleting records from a table with a significant
> > number or rows ( in this case 1.3 million ) it takes about 1 hour per
> > 100K rows deleted if deleting more then 400K at a time. This sounds
> > way to slow to me. If I break it up into smaller deletes then I can
> > usually get about 5K rows per minute, but even that seems awfully slow
> > to me.
> >
> > The machine is a pretty beefy box ( PIII 800Mhz, 256M, SCSI III )
> > and is basically doing nothing else. When performing the delete the
> > system goes to 100% utilization ( >95% is postmaster ) and stays that
> > way until the delete finishes. My guess is that the overhead of MVCC
> > tracking all of the rows deleted so that a rollback can be performed
> > is killing me here, but I can see no way to get around it.
> >
> > I have searched the on-line archives for all the postgres lists and
> > checked the FAQ and I can't find anything that gives any insight into
> > increasing delete performance.
> >
> > Any information whatsoever would be greatly appreciated.

--
_____
/ ___/___ | Bill Huff / bhuff(at)colltech(dot)com
/ /__ __/ | Voice: (512) 263-0770 x 262
/ /__/ / | Fax: (512) 263-8921
\___/ /ollective | Pager: 1-800-946-4646 # 1406217
\/echnologies |------[ http://www.colltech.com ] ------