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@colltech.com
   / /__  __/       | Voice: (512) 263-0770 x 262
  / /__/ /          | Fax:   (512) 263-8921
  \___/ /ollective  | Pager: 1-800-946-4646 # 1406217
      \/echnologies |------[ http://www.colltech.com ] ------