Deletes and large tables

From: Edmund Dengler <edmundd(at)eSentire(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Deletes and large tables
Date: 2005-06-10 14:37:22
Message-ID: Pine.BSO.4.58.0506101037140.19098@cyclops4.internal
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings!

We have a table with more than 250 million rows. I am trying to delete the
first 100,000 rows (based on a bigint primary key), and I had to cancel
after 4 hours of the system not actually finishing the delete. I wrote a
script to delete individual rows 10,000 at a time using transactions, and
am finding each individual delete takes on the order of 0.1 seconds to 2-3
seconds. There are 4 indexes on the table, one of which is very "hashlike"
(ie, distribution is throught the index for sequential rows).

Is this considered "normal" for delete speed? Currently using 8.0.1.
Drives are capable of 40MB/sec sustained transfers, but only acheiving
about 2-10kB/sec, and mostly CPU bound.

Regards!
Ed

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2005-06-10 14:50:01 Re: Deletes and large tables
Previous Message Shelby Cain 2005-06-10 14:12:28 Re: CPU-intensive autovacuuming