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
--
_____
/ ___/___ | 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 ] ------
