X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id A494111F6095 for ; Tue, 25 Apr 2006 18:41:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 38453-10 for ; Tue, 25 Apr 2006 18:41:05 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from pproxy.gmail.com (pproxy.gmail.com [64.233.166.177]) by postgresql.org (Postfix) with ESMTP id DD9E711F6089 for ; Tue, 25 Apr 2006 18:41:03 -0300 (ADT) Received: by pproxy.gmail.com with SMTP id x66so1484266pye for ; Tue, 25 Apr 2006 14:41:04 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=neQcjUZaU0HWUgs4n+tMjUKhIeDGv6O02e1Sr8dBWZermlXg7O5ES+R2Ugurpdx1cZJSwhZrYPDci3n1pRR+WibiomXRLarSKzGtsW7+QH8C4Y/rAt5Ivpe1nsmGnp/kahg7lyGsEhBHaSNfMY9ViaW8BOlmPE7QjuS321e6zeM= Received: by 10.35.40.10 with SMTP id s10mr187587pyj; Tue, 25 Apr 2006 14:41:03 -0700 (PDT) Received: by 10.35.82.19 with HTTP; Tue, 25 Apr 2006 14:41:03 -0700 (PDT) Message-ID: <8d04ce990604251441ya85c42evb47946272536cabe@mail.gmail.com> Date: Tue, 25 Apr 2006 14:41:03 -0700 From: "Junaili Lie" To: pgsql-performance@postgresql.org Subject: slow deletes on pgsql 7.4 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_6825_1133211.1146001263938" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/578 X-Sequence-Number: 18662 ------=_Part_6825_1133211.1146001263938 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi all, we encounter issues when deleting from a table based on id (primary key). O= n 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 >=3D 0) "$2" CHECK (organization_id >=3D 0) "$1" CHECK (subscriber_id >=3D 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 DELET= E 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=3D'1023', it takes less than 200 m= s. But when i do "delete from scenario where id=3D'1099', it took forever (mor= e than 10 minutes that i decided to cancel it. I can't do explain analyze, but here is the explain: MONSOON=3D# begin; BEGIN MONSOON=3D# explain delete from scenario where id=3D'1099'; QUERY PLAN ---------------------------------------------------------------------------= --- Index Scan using scenario_pkey on scenario (cost=3D0.00..3.14 rows=3D1 width=3D6) Index Cond: (id =3D 1099::bigint) (2 rows) MONSOON=3D# explain delete from scenario where id=3D'1023'; QUERY PLAN ---------------------------------------------------------------------------= --- Index Scan using scenario_pkey on scenario (cost=3D0.00..3.14 rows=3D1 width=3D6) Index Cond: (id =3D 1023::bigint) (2 rows) MONSOON=3D# explain analyze delete from scenario where id=3D'1023'; QUERY PLAN ---------------------------------------------------------------------------= --------------------------------------------- Index Scan using scenario_pkey on scenario (cost=3D0.00..3.14 rows=3D1 width=3D6) (actual time=3D0.028..0.030 rows=3D1 loops=3D1) Index Cond: (id =3D 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 =3D true. Any ideas what's going on? Thanks in advance, J ------=_Part_6825_1133211.1146001263938 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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.<= br>             &nb= sp;            =         Table "public.scenario"
     Column      |         Type          |            &n= bsp;      Modifiers
-----------------+-----------------------+---------------------------------= ---------------
 id           &= nbsp;  | bigint           &nb= sp;    | not null default nextval('scenario_seq'::text)
 name           = ; | character varying(50) |
 description     | text            = ;      |
 subscriber_id   | bigint           &nb= sp;    |
 organization_id | bigint       &nb= sp;        |
 schedule_id     | bigint           &nb= sp;    |
Indexes:
    "scenario_pkey" primary key, btree (id)
    "org_ind_scenario_index" btree (organization_i= d)
    "sch_ind_scenario_index" btree (schedule_id)     "sub_ind_scenario_index" btree (subscriber_id)=
Check constraints:
    "$3" CHECK (schedule_id >=3D 0)
    "$2" CHECK (organization_id >=3D 0)
    "$1" CHECK (subscriber_id >=3D 0)
Foreign-key constraints:
    "0_4774" FOREIGN KEY (schedule_id) REFERENCES = schedule(id) ON DELETE CASCADE
    "0_4773" FOREIGN KEY (organization_id) REFEREN= CES organization(id) ON DELETE CASCADE
    "0_4772" FOREIGN KEY (subscriber_id) REFERENCE= S subscriber(id) ON DELETE CASCADE

In all the child tables, the foreign key has the same data type and are ind= exed.
When I do "delete from scenario where id=3D'1023', it takes less than = 200 ms.
But when i do "delete from scenario where id=3D'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=3D# begin;
BEGIN
MONSOON=3D# explain delete from scenario where id=3D'1099';
            &nb= sp;            =          QUERY PLAN
---------------------------------------------------------------------------= ---
 Index Scan using scenario_pkey on scenario  (cost=3D0.00..3.14 r= ows=3D1 width=3D6)
   Index Cond: (id =3D 1099::bigint)
(2 rows)

MONSOON=3D# explain delete from scenario where id=3D'1023';
            &nb= sp;            =          QUERY PLAN
---------------------------------------------------------------------------= ---
 Index Scan using scenario_pkey on scenario  (cost=3D0.00..3.14 r= ows=3D1 width=3D6)
   Index Cond: (id =3D 1023::bigint)
(2 rows)

MONSOON=3D# explain analyze delete from scenario where id=3D'1023';
            &nb= sp;            =             &nb= sp;            =      QUERY PLAN
---------------------------------------------------------------------------= ---------------------------------------------
 Index Scan using scenario_pkey on scenario  (cost=3D0.00..3.14 rows=3D1 width=3D6) (actual time=3D0.028..0.030 rows=3D1 loops=3D1)
   Index Cond: (id =3D 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 =3D true.
Any ideas what's going on?
Thanks in advance,

J
------=_Part_6825_1133211.1146001263938--