Postgres Pro
Downloads
Home   >   mailing lists

Thread: Very slow DELETE on 4000 rows of 55000 row table

Very slow DELETE on 4000 rows of 55000 row table

From
Bryce Nesbitt
Date:
I've got a DELETE FROM that seems to run forever, pegging the CPU at
100%.  I can't figure out why it's slow.  Any clues?


stage=# EXPLAIN DELETE FROM EG_INVOICE WHERE PERIOD_ID = 1017506;
Index Scan using ix22f7bc70c7de2059 on eg_invoice  (cost=0.00..105.39
rows=3955 width=6)  Index Cond: (period_id = 1017506)

stage=# select count(*) FROM EG_INVOICE;
55376

stage=# select count(*) FROM EG_INVOICE where PERIOD_ID = 1017506;; 4603

stage=# \d EG_INVOICE;             Table "public.eg_invoice"    Column     |          Type          | Modifiers
----------------+------------------------+-----------invoice_id     | integer                | not nullcso_id         |
integer               | not nullperiod_id      | integer                | not nullinvoice_number | character
varying(192)|invoice_date   | date                   |plan_name      | character varying(128) |invoice_style  | integer
              | not nullaccount_id     | integer                |
 
Indexes:   "eg_invoice_pkey" PRIMARY KEY, btree (invoice_id)   "invoice_number_idx" btree (invoice_number)
"ix22f7bc70c7de2059"btree (period_id)
 
Foreign-key constraints:   "fk22f7bc70c7de2059" FOREIGN KEY (period_id) REFERENCES
eg_billing_period(period_id)   "invoice_to_account" FOREIGN KEY (account_id) REFERENCES
eg_account(account_id)   "invoice_to_cso" FOREIGN KEY (cso_id) REFERENCES eg_cso(cso_id)

stage=# vacuum analyze verbose EG_INVOICE;
...
INFO:  "eg_invoice": scanned 584 of 584 pages, containing 55376 live
rows and 0 dead rows; 3000 rows in sample, 55376 estimated total rows

PostgreSQL 8.1.8 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.1.1 20070105 (Red Hat 4.1.1-51)


-- 
----
Visit http://www.obviously.com/

Re: Very slow DELETE on 4000 rows of 55000 row table

From
Tom Lane
Date:
Bryce Nesbitt <bryce1@obviously.com> writes:
> I've got a DELETE FROM that seems to run forever, pegging the CPU at
> 100%.  I can't figure out why it's slow.  Any clues?

Unindexed foreign key constraints pointing to this table, perhaps?
EXPLAIN ANALYZE would give a great deal more clue than plain EXPLAIN.
        regards, tom lane

Re: Very slow DELETE on 4000 rows of 55000 row table

From
Bryce Nesbitt
Date:
Tom Lane wrote:
> Bryce Nesbitt <bryce1@obviously.com> writes:
>   
>> I've got a DELETE FROM that seems to run forever, pegging the CPU at
>> 100%.  I can't figure out why it's slow.  Any clues?
>>     
>
> Unindexed foreign key constraints pointing to this table, perhaps?
> EXPLAIN ANALYZE would give a great deal more clue than plain EXPLAIN.
>
>             regards, tom lane
>   
Hmm, excellent point.  There is a column with no index that points to
the table in question:

Indexes:   "eg_order_line_pkey" PRIMARY KEY, btree (order_line_id)   "ixf8331222783867cc" btree (order_id)
Foreign-key constraints:   "fkf8331222783867cc" FOREIGN KEY (order_id) REFERENCES
eg_order(order_id)   "fkf83312228edf278d" FOREIGN KEY (invoice_id) REFERENCES
eg_invoice(invoice_id)   "order_line_to_cso" FOREIGN KEY (cso_id) REFERENCES eg_cso(cso_id)

But I DELETE all conflicting those rows prior to the slow DELETE, just
so the FK check is never hit.  Should I be looking at subverting the FK
check mechanism somehow?  The necessary index would be huge, and
relevant only on this particular operation which happens every few
months, if that.

Re: Very slow DELETE on 4000 rows of 55000 row table

From
Richard Broersma Jr
Date:
> But I DELETE all conflicting those rows prior to the slow DELETE, just
> so the FK check is never hit.  Should I be looking at subverting the FK
> check mechanism somehow?  The necessary index would be huge, and
> relevant only on this particular operation which happens every few
> months, if that.

true, but without an index, it still has to scan the table just to be sure.

Re: Very slow DELETE on 4000 rows of 55000 row table

From
Tom Lane
Date:
Richard Broersma Jr <rabroersma@yahoo.com> writes:
>> But I DELETE all conflicting those rows prior to the slow DELETE, just
>> so the FK check is never hit.  Should I be looking at subverting the FK
>> check mechanism somehow?  The necessary index would be huge, and
>> relevant only on this particular operation which happens every few
>> months, if that.

> true, but without an index, it still has to scan the table just to be sure.

If this is only a once-in-awhile thing, maybe you could build the index,
do the deletes, drop the index ...
        regards, tom lane