pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedVery slow DELETE on 4000 rows of 55000 row table
5+ messages / 3 participants
[nested] [flat]
* Very slow DELETE on 4000 rows of 55000 row table
@ 2007-04-03 06:10 Bryce Nesbitt <bryce1@obviously.com>
2007-04-03 07:04 ` Re: Very slow DELETE on 4000 rows of 55000 row table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Bryce Nesbitt @ 2007-04-03 06:10 UTC (permalink / raw)
To: pgsql-sql
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 null
cso_id | integer | not null
period_id | integer | not null
invoice_number | character varying(192) |
invoice_date | date |
plan_name | character varying(128) |
invoice_style | integer | not null
account_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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Very slow DELETE on 4000 rows of 55000 row table
2007-04-03 06:10 Very slow DELETE on 4000 rows of 55000 row table Bryce Nesbitt <bryce1@obviously.com>
@ 2007-04-03 07:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-05 04:17 ` Re: Very slow DELETE on 4000 rows of 55000 row table Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2007-04-03 07:04 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Very slow DELETE on 4000 rows of 55000 row table
2007-04-03 06:10 Very slow DELETE on 4000 rows of 55000 row table Bryce Nesbitt <bryce1@obviously.com>
2007-04-03 07:04 ` Re: Very slow DELETE on 4000 rows of 55000 row table Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-04-05 04:17 ` Bryce Nesbitt <bryce1@obviously.com>
2007-04-05 04:46 ` Re: Very slow DELETE on 4000 rows of 55000 row table Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Bryce Nesbitt @ 2007-04-05 04:17 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Very slow DELETE on 4000 rows of 55000 row table
2007-04-03 06:10 Very slow DELETE on 4000 rows of 55000 row table Bryce Nesbitt <bryce1@obviously.com>
2007-04-03 07:04 ` Re: Very slow DELETE on 4000 rows of 55000 row table Tom Lane <tgl@sss.pgh.pa.us>
2007-04-05 04:17 ` Re: Very slow DELETE on 4000 rows of 55000 row table Bryce Nesbitt <bryce1@obviously.com>
@ 2007-04-05 04:46 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-04-05 05:23 ` Re: Very slow DELETE on 4000 rows of 55000 row table Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Richard Broersma Jr @ 2007-04-05 04:46 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; pgsql-sql
> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Very slow DELETE on 4000 rows of 55000 row table
2007-04-03 06:10 Very slow DELETE on 4000 rows of 55000 row table Bryce Nesbitt <bryce1@obviously.com>
2007-04-03 07:04 ` Re: Very slow DELETE on 4000 rows of 55000 row table Tom Lane <tgl@sss.pgh.pa.us>
2007-04-05 04:17 ` Re: Very slow DELETE on 4000 rows of 55000 row table Bryce Nesbitt <bryce1@obviously.com>
2007-04-05 04:46 ` Re: Very slow DELETE on 4000 rows of 55000 row table Richard Broersma Jr <rabroersma@yahoo.com>
@ 2007-04-05 05:23 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2007-04-05 05:23 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: Bryce Nesbitt <bryce1@obviously.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-04-05 05:23 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-04-03 06:10 Very slow DELETE on 4000 rows of 55000 row table Bryce Nesbitt <bryce1@obviously.com>
2007-04-03 07:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-04-05 04:17 ` Bryce Nesbitt <bryce1@obviously.com>
2007-04-05 04:46 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-04-05 05:23 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox