slow deletes on pgsql 7.4

From: "Junaili Lie" <junaili(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: slow deletes on pgsql 7.4
Date: 2006-04-25 21:41:03
Message-ID: 8d04ce990604251441ya85c42evb47946272536cabe@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi all,
we encounter issues when deleting from a table based on id (primary key). On
certain 'id', it took forever to delete and the i/o is 100% busy.
Table scenario has around 1400 entries. It is the parent of 3 other table.
Table "public.scenario"
Column | Type | Modifiers
-----------------+-----------------------+------------------------------------------------
id | bigint | not null default
nextval('scenario_seq'::text)
name | character varying(50) |
description | text |
subscriber_id | bigint |
organization_id | bigint |
schedule_id | bigint |
Indexes:
"scenario_pkey" primary key, btree (id)
"org_ind_scenario_index" btree (organization_id)
"sch_ind_scenario_index" btree (schedule_id)
"sub_ind_scenario_index" btree (subscriber_id)
Check constraints:
"$3" CHECK (schedule_id >= 0)
"$2" CHECK (organization_id >= 0)
"$1" CHECK (subscriber_id >= 0)
Foreign-key constraints:
"0_4774" FOREIGN KEY (schedule_id) REFERENCES schedule(id) ON DELETE
CASCADE
"0_4773" FOREIGN KEY (organization_id) REFERENCES organization(id) ON
DELETE CASCADE
"0_4772" FOREIGN KEY (subscriber_id) REFERENCES subscriber(id) ON DELETE
CASCADE

In all the child tables, the foreign key has the same data type and are
indexed.
When I do "delete from scenario where id='1023', it takes less than 200 ms.
But when i do "delete from scenario where id='1099', it took forever (more
than 10 minutes that i decided to cancel it.
I can't do explain analyze, but here is the explain:
MONSOON=# begin;
BEGIN
MONSOON=# explain delete from scenario where id='1099';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using scenario_pkey on scenario (cost=0.00..3.14 rows=1
width=6)
Index Cond: (id = 1099::bigint)
(2 rows)

MONSOON=# explain delete from scenario where id='1023';
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using scenario_pkey on scenario (cost=0.00..3.14 rows=1
width=6)
Index Cond: (id = 1023::bigint)
(2 rows)

MONSOON=# explain analyze delete from scenario where id='1023';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Index Scan using scenario_pkey on scenario (cost=0.00..3.14 rows=1
width=6) (actual time=0.028..0.030 rows=1 loops=1)
Index Cond: (id = 1023::bigint)
Total runtime: 0.174 ms
(3 rows)

I have also tried increasing statistics on both parent and child tables to
100, vacuum analyze parent and all child tables. But still the same
slowness.
The o/s is Solaris 10, with fsync = true.
Any ideas what's going on?
Thanks in advance,

J

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Ron Peacetree 2006-04-25 21:43:14 Re: Large (8M) cache vs. dual-core CPUs
Previous Message David Boreham 2006-04-25 21:15:06 Re: Large (8M) cache vs. dual-core CPUs